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