Sunday 15 November 2009

Database logins and roles script

this script readls the roles for a database server and then creates directly a script that can be run against a different server in order to re-crate logins and roles.




-- SCRIPT TO CREATE THE ROLES CREATION SCRIPT
-- IN ORDER TO MOVE THE DATABASES TO ANOTHER SERVER
-- This script needs to be run on the original Server
-- and it will output the necessary SQL instructions
-- that you will run on the new server to replicate
-- the original server roles configuration

-- Originally found on http://www.sqlservercentral.com/Forums/Topic508244-359-1.aspx -- Fabrice Faleni

--Role Memberships'
DECLARE @DBName VARCHAR(64)
DECLARE c1 CURSOR FOR

SELECT name
FROM MASTER..sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN c1
FETCH NEXT FROM c1
INTO @DBName
WHILE @@FETCH_STATUS <> -1

BEGIN

PRINT @DBName



EXEC( 'USE ' + @DBName + ' GO;')



SELECT --rm.role_principal_id,

'EXEC sp_addrolemember @rolename =' + Space(1) + Quotename(User_name(rm.role_principal_id),'''') + ', @membername =' + Space(1) + Quotename(User_name(rm.member_principal_id),'''') AS '--Role Memberships'

FROM sys.database_role_members AS rm

ORDER BY rm.role_principal_id



--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(Schema_name(obj.schema_id)) + '.' + Quotename(obj.name) COLLATE latin1_general_ci_as_ks_ws + CASE

WHEN cl.column_id IS NULL

THEN Space(0)

ELSE '(' + Quotename(cl.name) + ')'

END + Space(1) + 'TO' + Space(1) + Quotename(usr.name) + 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 usr.name



--Database Level 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
(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 --usr.name = @OldUser

--AND

perm.major_id = 0

ORDER BY perm.permission_name ASC,

perm.state_desc ASC

FETCH NEXT FROM c1
INTO @dbname
END
CLOSE c1
DEALLOCATE c1