To take a backup of SQL serve logins and permissions.
SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' AS '--Database Users Creation--'
FROM sys.database_principals
WHERE Type IN (
'U'
,'S','G'
)
AND NAME NOT IN (
'dbo'
,'guest'
,'sys'
,'INFORMATION_SCHEMA'
)
GO
SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '---Add Users to Database Roles---'
FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM
ON DBM.member_principal_id = DBUser.principal_id
INNER JOIN sys.database_principals DBRole
ON DBRole.principal_id = DBM.role_principal_id
GO
SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' AS '--Database Users Creation--'
FROM sys.database_principals
WHERE Type IN (
'U'
,'S','G'
)
AND NAME NOT IN (
'dbo'
,'guest'
,'sys'
,'INFORMATION_SCHEMA'
)
GO
SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '---Add Users to Database Roles---'
FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM
ON DBM.member_principal_id = DBUser.principal_id
INNER JOIN sys.database_principals DBRole
ON DBRole.principal_id = DBM.role_principal_id
GO
No comments:
Post a Comment