Thursday, 9 January 2014

Determine if user is a member of the SQL sysadmin role using C#

When using the SQL management objects (SQLXMO) from .NET you may wish to check if the user that is connecting to the SQL server in code is a member of the sysadmin fixed server role.

There are a couple of gotchas here

Firstly the following code can be used however often you will have Windows users that are members of a group and this group is then assigned permissions to a SQL server. In this case the code will fail with an null argument exception as the user is not explicitly listed in the logins.

Server.Logins[Server.ConnectionContext.TrueLogin].IsMember("sysadmin");

You also have to be careful to ensure that you are testing the user's nested group membership, for example if a user "dsmith" is a member of "IT Technical Support" and "IT Technical Support" is a member of "SQL Administrators" which are assigned the sysadmin role on all the databases we need to ensure that the nested group membership is evaluated.

The simplest solution is to call the IsInFixedServerRole() method of the connection context. This will return a boolean value that indicates whether the user is a member of the sysadmin role.

Server.ConnectionContext.IsInFixedServerRole(FixedServerRoles.SysAdmin)


For more information about our software take a look at our SQL Server Documentation Tool



No comments:

Post a Comment