Setting permissions on a database mirror database snapshot

Question: I’d like to use a database snapshot on a mirror copy of our production database so that business users can run reports on the mirror server. How can I grant the users permissions on the mirror database without also granting them permissions on the principal server?

Answer: This is a question that came up today while teaching database snapshot usage and internals in one of our Immersion Events and I thought it would be great to share the answer with a much wider audience.

Related: Reasons not to configure automatic failover for database mirroring

It’s a reasonably common requirement from management and/or financial controllers that the hardware being used as a mirror server for database mirroring is also used for query processing, thereby increasing the ROI for the hardware.

The only way to access the data in a mirror database is to create a database snapshot on the mirror database and then have users connect to the database snapshot to run their queries. This is because the mirror database itself is constantly undergoing recovery and so is inaccessible.

The question stems from the fact that a database snapshot is read-only, and so permissions cannot be changed in it (compared to the database on which the snapshot was created). This means that the user permissions to perform queries must already have been granted on principal database (so they are present in the mirror database too, so they are present in the database snapshot of the mirror database too). In this case, how can the users be prevented from just connecting to the principal database itself?

The trick I came up with is to change the login permissions of the users. This works because the login permissions are stored in master, not in the user database itself. In the scenario under consideration here, you could remove the user login permissions for the principal server and leave them alone on the mirror server. The permissions within the user database are therefore irrelevant and don’t pose any problem.

Just remember that using a database snapshot on a mirror database can potentially slow down redo operations on the mirror, leading to an increased redo queue (all the log that hasn’t yet been replayed in the mirror database) and subsequently causing a longer-than-expected failover time if a disaster occurs.

Related: Efficient index maintenance using database mirroring

Discuss this Blog Entry 2

on Mar 13, 2012
One caveat to remember is that if they have access to the principal server through another login, they will get the permissions of that user in the database even though they are accessing via a different login. For example, if user Domain\Sally is a member of both Domain\SalesUsers and Domain\Managers. SalesUsers are granted read access to mirrored database DB1 and then the login is removed, but Managers are granted access to the server and have read access to database DB2, Sally will be able to login as a member of the Managers group and will have read access to both DB1 and DB2.
on Mar 13, 2012
One caveat to remember is that if they have access to the principal server through another login, they will get the permissions of that user in the database even though they are accessing via a different login. For example, if user Domain\Sally is a member of both Domain\SalesUsers and Domain\Managers. SalesUsers are granted read access to mirrored database DB1 and then the login is removed, but Managers are granted access to the server and have read access to database DB2, Sally will be able to login as a member of the Managers group and will have read access to both DB1 and DB2.

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×