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. Let us learn about Server Role Member.

Most of the time, I get a 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 me 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');
GO

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

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

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.

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

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 (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Introduction to JOINs – Basic of JOINs
Next Post
SQL SERVER – Poll Result – What is Your Favorite Database?

Related Posts

5 Comments. Leave new

  • Hi Pinal
    you probably meant “role” instead of “roll” for the entry subject :)

    I saw this function used in the 70-443 EXAM

    Reply
  • Hi Pinal,

    Your article helped me. I have blogged about using this in PowerShell here

    Thanks,
    Sameer.

    Reply
  • 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

    Reply
  • Unable to use this to identify ‘NT Service\MSSQLSERVER’.

    Reply

Leave a Reply

Menu