November 10, 2016 at 11:59 am
I restored databases in a new server. The database level users and permissions transferred successfully upon restore.
How do I now transfer the logins , server roles (server level) and associate the database level users to the logins?
November 10, 2016 at 12:17 pm
for the logins themselves, I've found this KB article still works (last did it in February this year to SQL Server 2014 server)
https://support.microsoft.com/en-us/kb/918992
I have another script I use to get the server roles and MSDB database permissions:
/* server roles */
PRINT '-- server roles members'
DECLARE @sqlcmd VARCHAR(255)
DECLARE roles_curs CURSOR FOR
SELECT 'EXEC sys.sp_addsrvrolemember '''+pr.name+''', '''+pm.name+''''
FROM sys.server_role_members AS rm
INNER JOIN sys.server_principals AS pm ON rm.role_principal_id=pm.principal_id
INNER JOIN sys.server_principals AS pr ON rm.member_principal_id=pr.principal_id
OPEN roles_curs
FETCH NEXT FROM roles_curs INTO @sqlcmd
WHILE (@@fetch_status <> -1)
BEGIN
PRINT @sqlcmd
FETCH NEXT FROM roles_curs INTO @sqlcmd
END
CLOSE roles_curs
DEALLOCATE roles_curs
PRINT 'GO'
PRINT ''
GO
/* system database permissions */
USE msdb
PRINT '-- msdb roles members'
PRINT 'USE msdb;'
DECLARE @sqlcmd VARCHAR(255)
DECLARE roles_curs CURSOR FOR
SELECT 'EXEC sys.sp_addrolemember @rolename = ''' + r.name + ''', @membername = ''' + m.name + ''';' AS sqlcmd
FROM sys.database_role_members rm
INNER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name IN ('DatabaseMailUserRole','SQLAgentUserRole','SQLAgentReaderRole','SQLAgentOperatorRole')
AND m.name NOT IN ('dc_operator','MS_DataCollectorInternalUser','PolicyAdministratorRole','SQLAgentReaderRole','SQLAgentOperatorRole')
OPEN roles_curs
FETCH NEXT FROM roles_curs INTO @sqlcmd
WHILE (@@fetch_status <> -1)
BEGIN
PRINT @sqlcmd
FETCH NEXT FROM roles_curs INTO @sqlcmd
END
CLOSE roles_curs
DEALLOCATE roles_curs
PRINT 'GO'
GO
November 10, 2016 at 12:21 pm
almost forgot, to reassociate SQL Authenticated logins to their database users:
/* automate fix SQL auth logins */
DECLARE @login nvarchar(128), @sqlcmd nvarchar(1000);
DECLARE cur_fix CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.database_principals
WHERE TYPE = 'S'
AND name IN (SELECT name FROM sys.server_principals)
AND name NOT IN ('guest','INFORMATION_SCHEMA','sys','dbo')
ORDER BY name;
OPEN cur_fix;
FETCH NEXT FROM cur_fix INTO @login;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @login;
SET @sqlcmd = N'EXEC sp_change_users_login ''Auto_Fix'', ''' + @login + N'''';
EXEC sp_executesql @sqlcmd;
FETCH NEXT FROM cur_fix INTO @login;
END
CLOSE cur_fix;
DEALLOCATE cur_fix;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply