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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
IS_SRVROLEMEMBER('sysadmin', 'login') = 1
This function will return 1 if the given login is a member of the given role, 0 otherwise.
This is the way it's implemented in the system SPs that do anything requiring sysadmin.
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
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.