Hi there! In this blog post, we will explore a SQL query that allows you to list all database users with Permissions in SQL Server. As a DBA you must be able to identify which users have access to specific databases and what permissions they possess.
Understanding Database Users and Permissions
Before we dive into the SQL query, first let’s clarify the concepts of database users and permissions.
Database Users:
A database user is an individual or application that interacts with a SQL Server database.
Each user account is mapped to a Windows login or a SQL Server login.
Users are associated with roles and are granted permissions to perform specific actions within the database.
Permissions:
In SQL Server, permissions are used to control access to database objects, such as tables and views. Each user in a database has a set of permissions that determine what they are able to do within the database, such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE.
Query to List All Database Users With Database Role Permissions
USE TestDB -- Change the db Name GO SELECT ServerName=@@servername, Dbname=db_name(db_id()),p. name as UserName, p. type_desc as TypeOfLogin, pp. name as DatabaseRoleAssigned, pp. type_desc as TypeOfRole FROM sys.database_role_members roles JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id WHERE p. type_desc NOT IN('DATABASE_ROLE','ASYMMETRIC_KEY_MAPPED_USER') AND p. name NOT IN ('sys','INFORMATION_SCHEMA','guest','dbo')
Output:
Generally speaking the above output is neat. Point often overlooked is we only got the details of the users which are part of any database roles. In other words I still need to know about users having Execute permission to any Stored Procedure, SELECT/INSERT/UPDATE permission on specific table etc.
Given these points let’s look into the below query.
Query To List All Database Users With Permissions:
USE TestDB -- Change the db Name GO SELECT @@servername as SQLInstanceName, Dbname=db_name(db_id()), u.name as 'UserName', u.type_desc as LoginType, p.permission_name as 'Permission', o.name as 'ObjectName', o.type_desc as 'ObjectType' FROM sys.database_permissions p INNER JOIN sys.objects o ON p.major_id = o.object_id INNER JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
Output:
As can be seen in the above example output the query shows the permissions on specific objects. Hence you need these two queries to List All Database Users With Permissions.
Important to realize the following points:
- The queries returns information about the database roles and permissions on database-level. Therefore it will not return information about server-level permissions.
- Both the queries returns information about SQL and Windows users but does not include permission of roles. Hence If you want to see the permissions of database roles please feel free to tweak the query.
- These will only shows the permissions for the specific connected database . Hence to check the permissions for all users in all databases, run the query against each database individually or modify the query. You can refer the link Retrieve all permissions for a user in all user databases which provides assigned database roles for a login in all databases.
Conclusion:
In summary managing permissions in SQL Server is a crucial aspect of database administration. Hence I have provided a simple introduction of users and permissions before diving into the query.
With this in mind, you can easily use the queries to list all database users and their permissions. This will certainly help you maintain a secure and compliant database environment.
Regularly reviewing and auditing permissions is essential to ensure data integrity and security in your SQL Server databases.
