-1

I am using SQL Server 2008 R2.I am connected with Windows Authentication, I wanted to verify/check whether the Login had a sysadmin Server Role using code. Is there any script to verify the same?

Thanks in advance.

  • You want to check you're login if its sysadmin or other users? – KASQLDBA Sep 17 '15 at 5:57
  • I have logged in using windows authentication. I want to check what Role does the Login has. Does it have a sysadmin role or any other role? If there was a script that could provide the same when logged in using windows authentication. – user71934 Sep 17 '15 at 8:30
3
IS_SRVROLEMEMBER('sysadmin', 'login') = 1

This function will return 1 if the given login is a member of the given role, 0 otherwise.

See: https://msdn.microsoft.com/en-us/library/ms176015.aspx

This is the way it's implemented in the system SPs that do anything requiring sysadmin.

0

You can use sp_helpsrvrolemember

EXEC sp_helpsrvrolemember 'sysadmin'

Or else you can query the catalog syslogin view:

SELECT NAME, isntname FROM SYSLOGINS
WHERE sysadmin = 1 and hasaccess = 1

Using GUI:

Open the Management Studio. Connect to the SQL Server, expand Security node, and expand Server Roles. From the list of Server Roles right click sysadmin and choose Properties. Here it will list down all the sysadmin logins for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.