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.

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