I have a SQL 2008 production database which gets replicated using log shipping to a second, completely different SQL 2008 server. There is also another copy which is restored at irregular intervals to a third SQL instance, again on a completely separate server.
The primary instance of the database is the backbone of our case management system and as such, all rights to insert/update data are consigned by the CMS system itself using a series of permission groups. The secondary (log shipped) instance is used for reporting so that intensive queries aren’t kicking the daylights out of the CMS backend. The tertiary instance is used for development and testing of new code.
I don’t want my team of developers to be able to do anything on the Primary instance than have read-only access to the tables, and no ability to manipulate or execute stored procedures. I do however want them to be able to execute stored procedures on the Secondary instance (which is read-only anyway) and to have full access to the Tertiary instance.
If I only had to worry about individual user rights on the three instances, it would be easy, but there’s a snag: as well as having individual user accounts on all three SQL instances, there is also an Active Directory group which contains my team members but also contains a couple of other user accounts that DO need full access to the production database. That AD group also has permissions on MSDB to enable manipulation of the SQL agent.
So my question is this: if I have the team members set up with explicit per-user-per-database access on the Primary instance, will those permission sets take priority over any rights that might be conferred by membership of the AD group? The log shipping happens every 20 minutes throughout the day so I don’t want to set something up that necessitates having to change the permissions on the Secondary instance after every log-shipping occurrence.
Just to add a further complication, there is an additional database on the Primary instance that I DO want my team to have full control of. That additional database doesn’t get log-shipped though and is only restored to the Tertiary instance when I decide the need has arisen.
Best Answer
I don't know how much you already know about the user and permission system in SQL Server. It is possible that you need to do some studying in order to fit all pieces together, to your liking.
Restore (which includes log shipping) gives you a binary copy of the database. This includes all users in the database with the assigned database level (and lower) permissions. Nothing you can do about this. Except for running post-restore scripts, but that seems undesirable and also impossible since it means breaking log shipping.
Then you have the instance level. This means logins. The only privilege at the instance level that "trickle down" to the database level is sysadmin (including CONTROL SERVER). This allow somebody to do everything, including SELECT, INSERT ect on all tables in all databases. There are of course other server level roles and server level assignable permissions, but those are more "special things", and as far as I can see won't affect traditional object level privileges.
Permissions are cumulative. You have the sum of all privileges. But that excludes any DENY. DENY overrides all types of GRANT. (There is an exception with column level DENY, but my guess is that it won't affect you.) Well, except for sysadmin, DENY isn't checked for those who are sysadmin.
I've tried to follow your description to be more to the point, but I'm afraid I get a bit lost in the details. Perhaps above can get you started and you can get back with specifications or more precise questions?