Friday, July 11, 2014

Scripting Server Permissions And Role Assignments


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

-- Permissions 

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 ONserver_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 

1 comment:

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...