February 6, 2007 at 5:17 pm
I am attempting to use SETUSER, trap the error (if returned) and handle it accordingly. I get the following error, expectidly, when a non-sysadmin user attempts to run the proc:
Server: Msg 262, Level 14, State 4, Procedure ***** , Line 31
SETUSER permission denied in database *****
I would test, next if @@error = 262, but SQL Server 2000 won't move to the next line.
I am currently running a test with:
SELECT * FROM master..syslogins WHERE sysadmin = 1 AND name = SYSTEM_USER
but if the login is part of a group that has sysadmin, the login will not show in the list and I am SOL.
Anyone have a more creative way for testing if a user is a sysadmin? Or is there a way to make SQL Server move to the next line?
February 6, 2007 at 5:40 pm
Error trapping in SQL Server 2000 works on a hit and miss basis and what works with one Service Pack does not work with another. See Erland Sommarskog's home page at http://www.sommarskog.se/index.html for all the gory details about error handling.
Suggest you just "set xact_abort on" at the begining of the module which will cause the module to exit on an error.
For testing if a login is a member of a server role:
declare @IS_sysadmin integer
set @IS_sysadmin = IS_SRVROLEMEMBER ( 'sysadmin' )
IF @IS_sysadmin = 1 print 'is sysadmin'
IF @IS_sysadmin = 0 print 'is NOT sysadmin'
IF @IS_sysadmin is null print 'Role is not defined'
SQL = Scarcely Qualifies as a Language
February 7, 2007 at 7:33 am
Thank you Carl. IS_SRVROLEMEMBER kept slipping my mind and I couldn't find it on MSDN.
Cheers!
-Kevin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply