Consider the situation when you need to add a new database
User and SQL Server Login to Log shipped database.
The main problem here is that the Login in the Log Shipping
SQL Server must have same SID as Login in primary
SQL Server.
Here is a simple way to add new SQL Server Login with
same SID as in source server:
1. Create a new login on the primary SQL Server and map
it to required DB.
2. Execute this command in the primary SQL Server
to generate a script for creating the SQL Server Login
with same SID: Exec sp_help_revlogin 'LoginName'
Copy the script and execute it in Log Shipping SQL
Server.
3. After the restoring the transaction logs on the Log Shipping
machine, the new added database user should be mapped
to the new created SQL Server Login and you should be able
to connect/login to the Log Shipping Server with the new Login.
Hi Dan,
ReplyDeleteI hadn't sp_help_revlogin procedure.
After some research I found out that is should be created by DBA.
Here some relevant Microsoft links
How to transfer logins and passwords between instances of SQL Server
How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008
Thanks,
ReplyDeleteYou are absolutely right.
I forgot to mention it in the post.
Thanks for reading.
Dan