Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Am I a sysadmin? (or other SQL Server role)

How do you check if you are a sysadmin? It’s fairly easy to do in Management Studio. You can go to Security \ Server Roles \ Sysadmin, as shown here:

sysadmin1

You right click sysadmin and click properties to get a list of sysadmins. You can do this for any role, and that’s the easy way if you want to verify permissions.

sysadmin2

What if you have an open connection to the server, say in a Query window or Powershell session and want to verify your role. There’s a function to help you: Is_SrvrRoleMember().

If you execute something like this:

SELECT IS_SRVROLEMEMBER('sysadmin');

You’ll get a one back if you are a member of that role, and a 0 otherwise. That will allow you to easily determine your current permissions, or check permissions programatically and continue on with your work depending on the results.


Filed under: Blog Tagged: security, sql server, syndicated, T-SQL

Comments

Posted by Jason Brimhall on 5 April 2011

What a coincidence.  I posted a blog about the same topic today.

jasonbrimhall.info/.../sql-server-role-membership

Posted by dfortier on 8 April 2011

Very helpful, thank you.

Posted by sknox on 8 April 2011

Sure, this function will you tell if your account is a member of the sysadmin role, and your job description/contract will tell you if it's part of your duties, but determining if YOU are a sysadmin requires a rigorous psychological evaluation and hours of deep soul-searching. ;P

Posted by Jason Brimhall on 8 April 2011

sknox - you forgot the sleep deprivation.

Leave a Comment

Please register or log in to leave a comment.