Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Saturday, 22 April 2017

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