Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Checking Admin User - Stored procedure Expand / Collapse
Author
Message
Posted Tuesday, June 10, 2008 2:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211
Hi

In my application , i am veryfying the Given user has administration rights in MS SQL SERVER 200 or not

please see the following stored procedure which i created, any body tell me whether this method is sufficient to verify the admin user in sql server 2000, since i not sure and don't find much information about verifying admin users in net . any body has idea please let me knos

ALTER PROCEDURE ISADMINUSER ( @UserName VARCHAR(100), @IsAdmin BIT OUTPUT )
AS
BEGIN
DECLARE @dbaccess BIT
,@islogin BIT
,@issysadmin BIT

SELECT @dbaccess = hasdbaccess
,@islogin = islogin
FROM master.dbo.sysusers
WHERE name = LTRIM(RTRIM(@UserName))

IF (@dbaccess = 1) AND (@islogin = 1)
BEGIN
SELECT @issysadmin = sysadmin
FROM master.dbo.syslogins
WHERE name = LTRIM(RTRIM(@UserName))
IF @issysadmin = 1
BEGIN
SET @IsAdmin = 1
RETURN
END
ELSE
BEGIN
SET @IsAdmin = 0
RETURN
END

END
ELSE
BEGIN
SET @IsAdmin = 0
RETURN
END
END

Thanks in Advance
Post #514216
Posted Tuesday, June 10, 2008 10:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 7, 2014 12:18 PM
Points: 413, Visits: 1,438
Can you use "IS_SRVROLEMEMBER" function? (Check Book Online for the detailed info.)
Post #514606
Posted Tuesday, June 10, 2008 9:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211
Thanks
IS_SRVROLEMEMBER function is working

thanks once again
Post #514912
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse