SQL SERVER – Check if Current Login is Part of Server Role Member

I often work on consulting projects with umpteen clients from across the globe. The nature of the works I usually receive necessitates me to take on the role of a system admin. Now, this role is trailed by come common issues. This article revolves around one such concern.

Most of the time, I get login and DBA from my clients. However, sometimes I face login-related problems, primarily because my clients forget to confer admin rights on me. Anticipating this situation I perform a simple task, which saves my time and saves me from exasperation.  Whenever I receive a server login I run the following query to verify if  the client has assigned me the role of system admin or not.

SELECT IS_SRVROLEMEMBER('sysadmin');

If I have been assigned system admin role then the result will be 1 else it will be 0.

I have listed below valid roles that can be verified using the above function.

sysadmin
dbcreator
diskadmin
processadmin
serveradmin
setupadmin
securityadmin

If you pass an invalid role to the above function, it will return value NULL.

I hope my article is clear to all my readers. Please send me your feedback. I want my readers to come up with more issues that need to be tackled.

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

5 thoughts on “SQL SERVER – Check if Current Login is Part of Server Role Member

  1. Hi,
    I’m trying to generate a spreadsheet showing what server roles all my sql server users belong to. How can I do that?
    Really appreciate any help.
    Peter

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s