December 12, 2008 at 12:19 pm
I'm trying to find or create a script to list the logins with the associated users and their server roles for each database on a server. Does anyone have one or know the DMV's that I might use to find the information? I'm trying to compare user's roles between production and dev servers to make sure we don't miss anyone.
Thanks for the help,
Elizabeth
December 12, 2008 at 2:30 pm
Check out SQLPermissions from Idera. It's free and should give you what you're looking for.
http://www.idera.com/Products/SQLpermissions/Default.aspx
I'll post some scripts that show you this also. Tools are great but it's also good to understand what's going on under the covers for the times when your tools aren't available.
December 12, 2008 at 2:37 pm
This script generates a script to create database users, roles, permissions, etc. for the database you're currently in. It's based on this script from Narayana Vyas Kondreddi.
[font="Courier New"]
SET NOCOUNT ON
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'
-- Logins
SELECT 'CREATE USER [' + usr.name + '] FOR LOGIN [' + susr.name + ']'
+ CASE WHEN usr.TYPE = 'S'
THEN ' WITH DEFAULT_SCHEMA=[' + usr.default_schema_name + ']'
ELSE ''
END
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id
INNER JOIN sys.server_principals AS susr ON usr.sid = susr.sid
WHERE perm.permission_name = 'CONNECT'
AND susr.sid != 0x01
-- Role Members
SELECT 'EXEC sp_addrolemember @rolename =' + SPACE(1)
+ QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername ='
+ SPACE(1) + QUOTENAME(usr.name, '''') AS '--Role Memberships'
FROM sys.database_role_members AS rm
INNER JOIN sys.database_principals AS usr ON rm.role_principal_id = usr.principal_id
WHERE usr.is_fixed_role = 0
ORDER BY rm.role_principal_id ASC
-- Object level permissions
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END + SPACE(1) + perm.permission_name + SPACE(1) + 'ON '
+ QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id
AND cl.[object_id] = perm.major_id
WHERE usr.is_fixed_role = 0
AND usr.sid != 0x01
ORDER BY perm.permission_name ASC,
perm.state_desc ASC
-- Database permissions
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END + SPACE(1) + perm.permission_name + SPACE(1) + SPACE(1) + 'TO'
+ SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
AND usr.NAME != 'dbo'
ORDER BY perm.permission_name ASC,
perm.state_desc ASC
[/font]
December 12, 2008 at 2:39 pm
This script generates a script to grant server role memberships and permissions. I don't remember where I got it from or I would give credit.
[font="Courier New"]SET NOCOUNT ON
SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context'
-- Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
+ QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1)
+ QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC
-- Generate statements to alter SQL 2005 server permissions for SQL logins, Windows Logins, and Groups
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH ( NOLOCK )
INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ( 'S', 'U', 'G' )
ORDER BY server_principals.name,
server_permissions.state_desc,
server_permissions.permission_name
[/font]
December 12, 2008 at 3:18 pm
This is great! Thanks so much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply