November 18, 2015 at 7:22 am
I have been a sql developer for years but just got thrown into (or under the bus) a DBA role for a dev server. My boss wants only a few to be admins on the server but wants to allow a specific AD group to be able to create databases and admin what they create but not manage anything else. I could really use some help.
November 18, 2015 at 8:23 am
Are you bothered about the AD group modifying (alter, drop, restore) any of the DB's on the instance?
If not give them dbcreator, this will allow them to create, alter, drop, and restore any database.
Any DB they create they get db_owner rights.
If you are bothered about them doing alter, drop, and restore any database then as you posted in the 2014 forum, I would suggest a custom server role which only has create db rights
November 19, 2015 at 5:39 am
Is this in production or a non-prod environment?
If production, you might want to have a discussion with your boss about the issues of giving DB create permissions in a production environment. There are many dangers in allowing people to just willy-nilly create dbs and do whatever they want with them. Especially if they have read permissions on all the other production databases (data theft is a big issue).
Also, discuss with your boss the ramifications of database backups and restores. Yes, these people will be responsible for maintaining the databases they create, but do they actually know HOW to maintain those databases? What happens if they don't take backups? What happens if they think copying .mdf files is sufficient? And what happens when the disk drives run out of space? Which databases get priority over which other databases in cases of disaster recovery?
There's a lot more to this issue than just giving these people db create permissions and letting them admin the databases. The biggest question is who's going to police them to make sure they're admining correctly?
November 20, 2015 at 7:42 am
This is a non-production environment and I agree with everything you have said, thank you.
November 23, 2015 at 5:06 am
Glad we could help. Let us know how it goes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply