Sunday, 14 June 2020

Login backup before restoring databases on SQL Server

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

No comments:

Post a Comment