sp_help_revlogin does not create sysadmin,processadmin privilges on destionation while Database Migration/ Restore of Database on SQL Server

sp_help_revlogin does not create syadmin,processadmin privilges on destionation while Database Migration/ Restore of Database on SQL Server

Demo:

Now we are going to see DEMO of  source database from source to destination as destination db

Here are the login details on both source and destination

Username	Password	Role	login exists destination	Password in destination
Sourcedbreader	Sourcedbreader	reader	yes	Sourcedbreader
Sourcedbwriter	123456	writer	no	n/a
sourcesysadmin	password	sysadmin	no	n/a
sourcepublic	654321	Public	no	n/a
sourceprocessadmin	spa123	processadmin	yes	spa123456

Here noted down sourcedbreader and sourceprocessadmin exists on both source and destination

Here Source server you can see source master as see following

sourcedb have login as see below

now we are going to take backup as see below

backed up as success see below

Destination db as see below both source db reader and source process admin logins already exists on destination

Now we have updated one record in source as see below

kindly see the updated as see below in source database

Now we are taking Transaction log backup in source as below

Transaction log backup as success @ Source as see below

Now we are going to restore in destination as see below

Now we locate the Source db backup file to restore in destination  as see below

we should need to choose Relocate option must be ticked as see below

we have to choose overwrite option as see below

database backup restored on destination as see below

destination db restored on destination as see below

destination restored db table info on destination as see below

you can see before updated table info only showing on destination (that is up to full backed up data)

so we should need to get full info then we should need to restore both full backup and transaction log

backup now we re initiate restore operation from backup file as see below

choose file as see below

 when we have chosen backup file it shows different file path we should need to point correctly as

click eclipses and choose primary data file  as see below

now we have correct data file M D F file in correct path as see below

Now we need to choose secondary log file correctly as see below

now choose Relocate option as see below

now click Options tab on restore properties  and choose overwrite and restore with no recovery  to get more file (transaction log ) to be restored

after clicked Database restored success with restoring mode in database

database in restoring mode see below

Now we are going to restore transaction log backup as see below

above steps we should need to choose restore with recovery option to database in read/write mode

transaction log backup restored successfully

now we can see restored db in read/write mode with logins

we can see table shows latest data as we saw in source

Source db reader info in destination as differ in SID as it already exists on both

 Source db writer did not  exists on Destination

 Source db process admin also did not  exists on Destination

Solving Orphan users:

now  we should need to verify EXEC s p_change_users_login report to check any orphan users existing or not

we have to create EXEC s p_help_rev login on source to capture login

we have to apply EXEC s p_help_rev login on destination to create login as we already captured on

source on above picture

now we need to verify EXEC s p_change_users_login report to check any orphan users existing or not after created logins above on destination

after created login both source and destination S I D same for source db writer as see below

after created login both source and destination S I D DIFF for source db reader it already exists on both

to solve orphan users we need to run s p_change_users_login update_one option as see below

After execute above source db reader SID same as see below

you can see we can login on destination all login (already exists and new login ) as see below

you can see after apply s p help rev login does not provide admin level privileges on destination from source as see below

source sysadmin does not have sysadmin

source process admin does not have process admin

To resolve this issue we need to execute following script on source

/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SET NOCOUNT ON;

SELECT  'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context';

-- Role Members
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM   sys.server_principals AS usr1
                                INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
                                INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC;

-- Permissions
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
                                + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM   sys.server_permissions AS server_permissions WITH (NOLOCK)
                                INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S', 'U', 'G')
ORDER BY server_principals.name ,
                                server_permissions.state_desc ,
                                server_permissions.permission_name;

now we need to copy above and execute it on destination as see below

Now you can see source sysadmin have sysadmin as see below

we can do above method or we can do manually as per below script to capture details

USE master
GO
SELECT  @@servername as servername,p.name AS [loginname] ,
        p.type ,
        p.type_desc ,
        p.is_disabled,
        CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
        CONVERT(VARCHAR(10),p.modify_date , 101) AS [update],sysadmin,processadmin
FROM    sys.server_principals p
        JOIN sys.syslogins s ON p.sid = s.sid
WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        -- Logins that are sysadmins
        AND s.sysadmin = 1-----------to know all sysadmin login
OR s.processadmin=1-----------to know all processadmin login