I Give up - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

I Give up

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-08-19 : 07:01:28

I can't find out what is wrong with this, I've tried a number of variations and nothing seems to be working how I expect it to.

My problem is this. I have a table called LAB, within that table there is a field called INTID. I only want authorised users (i.e. members of a SQL group called LAB_admin) to be able to change the contents of this field and I can't alter the interface so I am trying to use an update trigger. In addition, if the esisting value in the record is null then I need to allow anyone (not just LAB_Admin) to be able to enter a value.

What I have tried is this:-

CREATE TRIGGER [dbo].[trg_LabIntid] on [lab]
/**************************************************************************
This trigger is designed to stop anyone changing the INTID
unless they are authorized

****************************************************************************/

for Update as

-- If the field is null then ignore this as we allow a number to be entered
-- If the field is not null then dont allow it to be changed unless the user is
-- a member of LAB_Admin

if (SELECT lab.intid from lab inner join inserted on lab.labno = inserted.labno) is not null

begin

-- check if the user is in the Lab Admin Group on the server
IF (SELECT IS_MEMBER ('LAB_Admin')) <> 1
BEGIN
print ('Member of LAB_Admin')
end
else
begin
print ('not a member of Lab admin')
-- raise custom error
RAISERROR ('Illegal attempt to alter INTID in LAB table.',14,1)-- WITH LOG
-- ensure that nothing is stored other than the error message
ROLLBACK TRANSACTION
end
end

go

This is to be run on SQL Server 7, when I try to run it, it seems to ignore the LAB_admin part so I can either change values or not change values but it isn't dependent on my membership of the group. Is this something to do with NULL's? Surely as the group LAB_Admin exists on the server then the line IF (SELECT IS_MEMBER ('LAB_Admin')) will never return a NULL Value and so should be ok shouldn't it?

I don't really have any other ideas what to look at.

Thanks in advance

steve

Steve no function beer well without

Kristen
Test

22859 Posts

Posted - 2004-08-19 : 07:37:57

Change:

if (SELECT lab.intid from lab inner join inserted on lab.labno = inserted.labno) is not null

to

if EXISTS (SELECT * from lab inner join inserted on lab.labno = inserted.labno WHERE lab.intid is not null)

perhaps? (If not I'll go back and read it carefuly!)

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-08-19 : 08:28:25

Sorry Kristen that won't let me do it whether I am a member of the group or not still.

Just out of interest, while I am using QA, if I change whether I am in a group do I need to reconnect to the server for this to become active? Surely not!

steve

Steve no function beer well without

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-19 : 08:55:06

i got a weird feeling when reading this, that all of your users acctualy connect with the same username.
that you check username and password which are stored in a table, and on login is checked with this table.
i'm wrong, right?

Go with the flow & have fun! Else fight the flow :)

Kristen
Test

22859 Posts

Posted - 2004-08-19 : 09:03:27

ROLE change should be instant, and for all existing connections.

You/Your User is connecting with their own ID, and not an "intermediate" ID used by the application? (e.g. IIS normally connects with the same UserID regardless of how is actually using the web page)

Probably best to mess around with QA first, deliberately logging in using the UserID, rather than sa-equivalent, but it looks like you are dogint hat already - just checking!

'LAB_Admin' is a SQL Role on the current database?

This shows one row?


SELECT TOP 10 *
FROM	sysusers
WHERE	name = 'LAB_Admin'

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-19 : 09:15:56

ok... why didn't i say it like that???? :))))) doh...

Go with the flow & have fun! Else fight the flow :)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-08-19 : 09:20:29

The users will be using their Windows Domain login name to get at the system apart from the odd one who will be using a SQL login.

There is no web interface, access is through MS Access and ODBC

The group is in the current database.

Is this just one of those wierd things? Might their be a better way of doing this?

steve

Steve no function beer well without

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-19 : 09:36:33

try this in your sproc :

select CURRENT_USER

and see what that returns you.

Go with the flow & have fun! Else fight the flow :)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-08-19 : 09:43:26

Ah, that just says dbo and not my user name (which is elwoos)

I presume that I need to use another - less priveliged login which is also what Kristen said. Oops!

steve

Steve no function beer well without

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-19 : 09:48:50

probably... because dbo has sa privileges and access to everything

Go with the flow & have fun! Else fight the flow :)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-08-19 : 09:52:24

That seems to have cracked it.

D'oh

Many thanks to both for your help.

<How many more thick days do I need to make it to Stoopid I wonder?>

steve

Steve no function beer well without

Kristen
Test

22859 Posts

Posted - 2004-08-19 : 10:44:06

Confusious he say "Post a thousand message and still only get to Test" ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources