Roles within Microsoft SQL Server help database administrators manage permissions to structured data. Server-level roles, as their name suggests, grant access server-wide. Each SQL database can also have its own unique permissions and roles.
To maintain security and comply with many regulations, including PCI DSS and HIPAA, you need to have visibility over all server and database roles assigned to each user, and that can prove to be a complex task using native tools.
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. You can use stored procedures to build custom reports, but you will have to use queries to do this. For example, server-level role membership info is stored in the server_role_members system view of the master database. As IDs for principals 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 linked by ID number.
Users can view their own server role membership and the principal ID of each member of the fixed server roles, but to be able to view all server role memberships, additional permissions or membership in the securityadmin fixed server role are required.
To gather database-level information, you have to query SQL Server database roles on each database individually, which can be quite time consuming. Also, keep in mind that roles can be nested: database users, application roles and other database roles can be members of a database role.
Listing SQL Server Roles for a User
- Start Microsoft SQL Server Management Studio (MSSMS)
- From the File menu, click Connect Object Explorer
- 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, check Remember password
- Click Connect
- 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\username'
- Click Execute
- Review the list of server-level roles and principals (member names) in the query execution results:
Listing Database Roles in SQL Server for a User
- Start Microsoft SQL Server Management Studio (MSSMS)
- On the File menu, click Connect Object Explorer
- 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
- Click Connect
- Upon connection, select the Database you need to query for user roles
- Click New Query and paste the following script into the query field:
SELECT roles.principal_id AS RolePrincipalID, roles.name AS RolePrincipalName, database_role_members.member_principal_id AS MemberPrincipalID, members.name AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members
JOIN sys.database_principals AS roles
ON database_role_members.role_principal_id = roles.principal_id
JOIN sys.database_principals AS members
ON database_role_members.member_principal_id = members.principal_id;
GO
- Click Execute
- Review the list of server-level roles and principals (member names) in the query execution results:
How Lepide Auditor Helps Audit SQL Server
In summary, getting comprehensive information on current user roles with native tools can be extremely complex.
An alternative solution is to use the Lepide Auditor for SQL Server. This solution provides you with a simple means of seeing who, what, where, and when changes are made to your SQL servers and can automatically send you real-time or threshold-based alerts and detailed reports when needed. It even helps you keep track of the overall health of your SQL Servers.
Lepide SQL Server Reports
Here are two examples of SQL Server Reports from the Lepide Data Security Platform. The first is the All Server Object Changes Report and the second is the All Database Object Changes Report.
The information these reports show includes Who made a change, When it was made, the specific Operation carried out and Where from.
The reports can be filtered, grouped and exported.