MSSQL – List Users With Permissions

Sometimes you really just need to know how many users you have on your database, the below script will give a quick report for your database.

Note: This script is designed to run on a selected database, you will have to select the database first and then run the script.

SELECT 
	@@Servername AS [SQL Server],
	DB_NAME() AS [SQL Database],
	memberdatabaseprincipal.NAME AS [SQL User],
	princ.NAME AS [SQL Role],
	--memberdatabaseprincipal.type_desc AS member_type_desc,
	memberdatabaseprincipal.create_date AS [SQL User Create Date],
	memberdatabaseprincipal.modify_date AS [SQL User Modified Date]
FROM sys.database_principals AS princ
LEFT JOIN sys.database_permissions AS PERM ON PERM.grantee_principal_id = princ.principal_id
INNER JOIN sys.database_role_members AS roles ON roles.role_principal_id = princ.principal_id
INNER JOIN sys.database_principals AS memberdatabaseprincipal ON memberdatabaseprincipal.principal_id = roles.member_principal_id
WHERE memberdatabaseprincipal.NAME NOT IN ('dbo')
ORDER BY [SQL Role],
	[SQL User]