August 8, 2017 at 1:38 pm
Based on recent audits and policy changes, we've been trying to tighten security around our enterprise and taking away sysadmin from anyone not on the central DBA team. Developers are used to having it at the lower levels and it's been a struggle to take it back. However, we do still need them to perform some tasks (offshore 24/7) without our involvement. Here's the situation, names have been simplified for posting.
Bob (developer) has domain account ABC\Bob
ABC\Bob is a member of domain groups ABC\AppTeam_Prod & ABC\AppTeam_Test
On ServerProd, the ABC\AppTeam_Prod has no server level role, but is db_owner for AppDB database.
On ServerTest, the ABC\AppTeam_Test has db_creator role.
ABC\Bob restores a backup of AppDB onto ServerTest successfully.
AppDB on ServerTest shows ABC\Bob as the owner under properties.
When trying to open database in SSMS, Bob get "The database AppDB is not accessible".
When trying to [USE AppDB] via t-sql is given "the server principal ABC\Bob is not able to access the database under the current security context"
Looking deeper....
The owner of dbo role is SA, even though owner of database is ABC\Bob.
The ABC\AppTeam_Prod group does not exist on the ServerTest and the ABC\AppTeam_Test is not yet in the restored copy.
Why did his login not get recognized as a member of the domain group? Does the old group need to have a server login as well?
Short of giving Bob sysadmin, what is the minimum security needed to allow him to restore backup copies from other servers and be able to access them?
I want him to be db_owner of whatever he puts on ServerTest, but do not want him having additional server level rights.
This is just the first scenario. There will be dozens of "ServerTests" restoring from dozens of "ServerProds", most with unique group names (used for other purposes within app), so I need a solution that will be standard and flexible. Hard coding additional groups/users in every possible combination is not feasible. Giving them sysadmin is not an option.
August 11, 2017 at 1:40 pm
The ABC\AppTeam_Test group has server level permission, and thus will be able to create (restore) a database, but since the group does not have specific database level permission, that is why the group, or more specifically, the person in the group, will not be able to access the restored database after restore.
I ran into this issue a few years ago and set up some sort of "triggering mechanism" that read the msdb table that tracks restores, and whenever a new restore entry was populated in the table, the "trigger" (I keep putting that in quotes because I don't think it was an actual trigger...but I don't remember exactly) would fire and grant db_owner on all user databases.
Now, as you might imagine, that was for a very specific set of circumstances--1. The test instance was only for developers, and any user database on the instance was their "playground"...so they automatically should have db_owner...therefore, if, in your situation, there is a difference in who should access the databases on that instance, then my suggestion won't work for you--or won't work exactly like what I implemented; 2. The AD group already existed on the test instance and like your scenario, had db_creator permission--so the result of my auto-magic implementation, I was only changing database permissions after a successful restore--oh yeah, keep in mind that a failed restore is captured in the same msdb table as successful ones...which may be an issue to keep an eye on; I didn't have to worry about it, because if a restore failed, the "auto-magic" process would still succeed since it was granting db_owner permission on all existing databases.
Sorry to ramble and be a little vague (not purposely...just memory lapse), but I hope this at least points you in the right direction.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 11, 2017 at 1:57 pm
Thanks for the input. I've gotten a workaround in place, but not was hoping there was a better solution.
Currently I created a custom server level role that includes db_creator and a few other settings, call it AppDBA.
Then I created a job that will look up who is a member of the AppDBA role and assign them db_owner to all non-system databases.
Since the ABC\AppTeam_Test also has SQLAgentOperator (to create own jobs), they can run this one.
Going forward we will always use the same server level role enterprise wide, and the same job enterprise wide. Then it doesn't matter which group name we assign they role, they will get ownership of their stuff.
Not quite as automated as your trigger method, and still not foolproof, but seems to work at the moment. Still open to suggestion if there is something I'm missing sounds like SQL_Hacker hit the same wall I did.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply