Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I was doing some security testing and wondered if I was a sysadmin. There are a few ways to check this, but I thought there should be a function to tell me.
There’s this code, of course:
SELECT
ServerRole = rp.name,
PrincipalName = SP.name
FROM sys.server_role_members rm
Inner JOIN sys.server_principals rp
ON rm.role_principal_id = rp.principal_id
Inner JOIN sys.server_principals SP
ON rm.member_principal_id = SP.principal_id
where sp.name = SUSER_SNAME()
and rp.name = ‘sysadmin’
That lets me know if my login is a sysadmin. However, there is a function that you can use. IS_SRVROLEMEMBER() is a function that you can use, passing in a server role as a parameter. The code I’d use to check on sysadmin membership is this:
SELECT IS_SRVROLEMEMBER(‘sysadmin’);
If I run this, I get a 1 if I’m a member, or a 0 if I’m not.
Using this function in your code allows you to make decisions based on role membership for the users involved, and perhaps alert them of needs for certain rights.
SQLNewBlogger
This was a quick one, really about 10 minutes to organize and write. Most of the time was writing the code to join system tables. If you tackle this subject, talk about how you might use this, or where this type of check could come in handy in your code (maybe before taking some action).
