How to List User Roles in SQL Server


Native Auditing vs. Netwrix Auditor for SQL Server
We never share your data. Privacy Policy
Native Auditing Netwrix Auditor for SQL Server
Steps

Listing SQL Server roles for a user

  1. Start Microsoft SQL Server Management Studio (MSSMS).
  2. On the File menu, click Connect Object Explorer.
  3. In the Connect to Server dialog box, specify the following settings:
  • In the Server type list box, select Database Engine.
  • In the Server name text box, type the name of the SQL cluster server.
  • In the Authentication list box, choose your SQL Server Authentication method and specify the credentials to use. If you do not want to re-type the password every time you connect to the server, tick Remember password.
  1. Click Connect.
  2. Upon connection, click New Query and paste the following script into the query field:

select r.name as Role, m.name as Principal

from

    master.sys.server_role_members rm

    inner join

    master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = 'R'

    inner join

    master.sys.server_principals m on m.principal_id = rm.member_principal_id

where m.name = 'ENTERPRISE\J.Carter'

  1. Click Execute (or hit the F5 key).
  2. Review the list of server-level roles and principals (member names) in the query execution results:
How to List User Roles in SQL Server - Native Auditing -  Server-Level

 

 

Querying database roles in SQL Server for a user

  1. Start Microsoft SQL Server Management Studio (MSSMS).
  2. On the File menu, click Connect Object Explorer.
  3. In the Connect to Server dialog box, specify the following settings:
  • In the Server type list box, select Database Engine.
  • In the Server name text box, type the name of the SQL cluster server.
  • In the Authentication list box, choose your SQL Server Authentication method and specify the credentials to use. If you do not want to re-type the password every time you connect to the server, tick Remember password.
  1. Click Connect.
  2. Upon connection, select the Database youneed to query for user roles.
  3. Click New Query and paste the following script into the query field:

SELECT r.name role_principal_name, m.name AS member_principal_name

FROM sys.database_role_members rm 

JOIN sys.database_principals r 

    ON rm.role_principal_id = r.principal_id

JOIN sys.database_principals m 

    ON rm.member_principal_id = m.principal_id

where m.name = 'ENTERPRISE\J.Carter'

  1. Click Execute (or hit the F5 key).
  2. Review the list of server-level roles and principals (member names) in the query execution results:
How to List User Roles in SQL Server - Native Auditing -  Database-Level
  1. Open Netwrix Auditor and navigate to Reports -> Predefined -> SQL Server - State-in-Time -> Account Permissions in SQL Server.
  2. Specify the following filters:
  • In the User account filter, type the full user name (such as ENTERPRISE/J.Carter).
  • In the Object type filter, choose Server Instance, Database.
  1. Click View to see a clear report on the effective permissions for the user.
How to List User Roles in SQL Server - Netwrix Auditor

Discover how to check user roles in SQL Server without a single query

Microsoft SQL Server provides roles to help database administrators manage permissions to structured data. Server-level roles, as their name implies, grant access server-wide, similar to groups in the Windows world. Each SQL database can also have its own unique permissions and roles. 

In order to maintain security and comply with many regulations, including PCI DSS and HIPAA, you need to know all the server and database roles assigned to each user. That’s a tough job if all you have are native tools because of all the complexity involved.

To start with, server-level settings, such as server roles, permissions, user credentials and dependencies are stored in the master database. Using the server_principals system view, you can see data for all the types of server principals: 

  • S = SQL login
  • U = Windows login
  • G = Windows group
  • R = Server role
  • C = Login mapped to a certificate
  • K = Login mapped to an asymmetric key
  • The public role sets the basic default permissions for all server users; it is assigned to every user automatically.
  • The sysadmin role grants administrative privileges on all server databases and assets.
  • Anyone with the db_owner role can perform all configuration and maintenance activities on the database.

To see database roles, however, you need to go to the database_principals system view, though at least they are typed in a similar way.

There are several common fixed roles with pre-defined set of permissions:

While stored procedures can assist you in managing areas of the server, to build custom reports (for example, one that matches several tables by specific column names), you will have to use queries. For example, server-level role membership info is stored in the server_role_members system view of the master database. Since principals’ IDs are linked, you can get a summary of SQL Server user roles with a query by joining sys.server_principals with master.sys.server_role_members based on ID number. Although users can view their own server role membership and the principal ID of each member of the fixed server roles, keep in mind that viewing all server role membership requires additional permissions or membership in the securityadmin fixed server role.

To gather database-level information, you have to query SQL Server database roles on each database individually, which can be quite time consuming. Moreover, roles can be nested: Database users, application roles and other database roles can be members of a database role. 

In short, getting comprehensive information on current user roles with native tools can be exceedingly complicated and downright exhausting. With Netwrix Auditor, on the other hand, you can get detailed information on who holds which server and database roles in a readable format in a few clicks. The linked reports included in the product enable your specialists to easily sift through the complications of nested user role membership across an entire server, streamlining investigations. You get all the critical details you need: a list of every object the user has access to, with its path and object type, the permissions that were granted, how they were granted (directly, through role membership, etc.), and whether they are explicit or inherited. You can analyze permissions from different angles, including the account level, the object level and the server level. All this information is clearly presented and available without the hassle of writing scripts, exporting information into Excel and manually sifting through it.