1

We are looking at mirroring a MS SQL database (for a web application) due to the unique way the application was developed, each user has a SQL server Login user account which has permissions to our web app database.

We have been unable to find out or make the MS SQL database mirror the user accounts from one server to the other.

Is it possible to do this or is MS SQL mirroring limited to the database tables / data only.

4 Answers 4

2

Database mirroring is limited to database only and does not include any information at the instance level, such as logins and jobs. These will need to be migrated over using a script or manual process. Microsoft has provided a stored procedure called sp_help_revlogin that will export the logins (and the SIDs associated with your SQL users) and the output of this script can be run on the DR server. This will create the logins with the same password, and since the database users (not logins, those are separate) are mapped to the instance logins using SIDs and the SIDs are still the same this should take care of your issue. You will have to script out the SQL jobs separately, though I think Bill Graziano has a scripting utility that does both of these these functions and can be scheduled, though I cannot find the link to it right now.

Found the link.

1

It's limited to databases only.

3
  • So what would be my best bet for backing up/ mirroring the logins Apr 10, 2012 at 15:36
  • You could script that, or just remember to create the logins on both servers each time you create one. Or use integrated authentication (assuming both servers are in the same domain.)
    – mfinni
    Apr 10, 2012 at 15:43
  • @aspect there's not a built-in solution to this that I know of. Creating the logins on both servers or changing your app are the most straightforward from the SQL Server side.
    – sh-beta
    Apr 10, 2012 at 15:45
1

MSSQL Database mirroring does not update mutual security or login settings.

However, you can use Windows integrated security and add new users to a "webapp" security group.

2
  • Are you sure about that? I was under the impression that replication was also data-only. Can you link to documentation?
    – sh-beta
    Apr 10, 2012 at 15:48
  • Not sure, no. meh, I'll remove that part
    – adaptr
    Apr 10, 2012 at 15:49
1

SQL Server 2012 introduces contained databases ( http://msdn.microsoft.com/en-us/library/ff929071.aspx) - essentially these are databases which are somewhat isolated from the rest of the SQL Server instance.

By setting your database to partial containment you can created contained users - the credentials and privileges are stored in the actual database rather than in master and so a contained user authenticates against that database rather than the SQL Server instance. As a result if you are using database mirroring the login and permissions are automatically transferred to the mirror.

To create contained users you also need to set the sp_configure option 'contained database authentication' to 1 on both the principal and mirror ( http://msdn.microsoft.com/en-us/library/ff929237.aspx)

There are a few security best practices to follow to be sure your database stays secure - see http://msdn.microsoft.com/en-us/library/ff929055.aspx for more.

If you aren't using SQL 2012 then I realise this is not much use, but perhaps it's a good reason to upgrade!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .