Monday, September 9, 2013

Copying database users to another database in SQL Server


--- Script to generate create users script from database ---

SELECT 'CREATE USER [' + name + '] for login [' + name + ']', type, type_desc from sys.database_principals
where Type in ('U', 'S', 'G') and name not in ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA')


-- Script to generate create role script from database
select distinct 'Create Role ' + r.name , r.is_fixed_role
 --[Role] = r.name, [Member] = m.name
from sys.database_role_members rm  join sys.database_principals r on rm.role_principal_id = r.principal_id     join sys.database_principals m on  rm.member_principal_id = m.principal_id
where r.is_fixed_role <> 1


--- Script to Add necessary members to roles ---

SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + ''''
from sys.database_principals users inner join sys.database_role_members link
on link.member_principal_id = users.principal_id inner join sys.database_principals roles
on roles.principal_id = link.role_principal_id

-----Script to generate script for object level permissions (Remove public and other permissions manually from the list)
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(USER_NAME(usr.principal_id)) 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
ORDER BY perm.permission_name ASC, perm.state_desc ASC


-- Generate script to list permisions for db roles
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(USER_NAME(usr.principal_id)) 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
ORDER BY perm.permission_name ASC, perm.state_desc ASC

--- List object level permissions
DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table


The below steps helps to copy users from one database to other database if you have large number of users, the above queries are copied from various sites
 

No comments: