January 24, 2015 at 7:10 am
i have created a new login in principle server and provided dbowner permission to principle db.
how do i transfer this login to mirror server and assign the same permission to mirror db ?
Thanks
January 25, 2015 at 1:11 pm
Whatever you did to add the login on the primary, you need to do on the secondary.
There are scripts that will pull out information for a login from the instance, and you can run those and get a script to run on the secondary, but there's no good automated way to do this.
January 26, 2015 at 1:44 am
Make sure, when you create the login on the mirror, you specify the SID in the CREATE statement, otherwise it won't match the database user.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2015 at 9:36 am
check out sp_help_revlogin.
database level permissions will come over as part of the mirrring replication.
You need to check for any server roles granted and server level permissions as well. Also worth scripting out default language for the logins.
---------------------------------------------------------------------
January 26, 2015 at 10:53 am
RUNNERSQL (1/24/2015)
i have created a new login in principle server and provided dbowner permission to principle db.how do i transfer this login to mirror server and assign the same permission to mirror db ?
Thanks
The script Kevin supplied in your other post will help you
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 26, 2015 at 1:12 pm
This can somewhat be automated using SQLCMD or Powershell. If you can setup contained databases it is much easier to just create the user in the database and let that transfer across - then the users can connect to that database directly without a login.
You should also consider disabling or dropping the login on the primary after the new user and login have been created on the secondary. You really don't want users to accidentally access the primary system and run reports.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply