Quick one today:
Quite often, we need to review and security in all our production and non-production environments to be sure that each account has the least number of permissions it needs to perform its operations (BEST PRACTICE)
As part of it, we gather this data occasionally for further review.
Gather all users in each database and their role assignments (both fixed roles & user defined roles).
-- -- List all users in each database and their role assignments -- SELECT DB_NAME(), RP.type_desc AS [RoleType], RP.name, UP.name, UP.type_desc [UserType], UP.default_schema_name AS [User_Schema] FROM sys.database_role_members AS R INNER JOIN sys.database_principals AS UP ON UP.principal_id = R.member_principal_id INNER JOIN sys.database_principals AS RP ON RP.principal_id = R.role_principal_id ORDER BY RP.name, UP.name GO
Once we wrap this into sp_MSforEachDB procedure, then we could run it for all databases.
-- -- List all users in each database and their role assignments -- EXEC sp_msforeachdb 'USE [?]; SELECT DB_NAME(), RP.type_desc AS [RoleType], RP.name, UP.name, UP.type_desc [UserType], UP.default_schema_name AS [User_Schema] FROM sys.database_role_members AS R INNER JOIN sys.database_principals AS UP ON UP.principal_id = R.member_principal_id INNER JOIN sys.database_principals AS RP ON RP.principal_id = R.role_principal_id ORDER BY RP.name, UP.name ' GO
It you use multi-script, we could gather this data across all instances in your environment as well.
_Sqltimes
[…] also have a need to retrieve all the server roles a login is associated with. Earlier we covered a similar post where covered a script to retrieve all database users & their database role assignments; In […]
[…] the past, we’ve seen some dynamic between Roles, Logins, Users & Permissions. Today, we’ll look into a quick way to a query to retrieve permissions on […]