January 27, 2017 at 6:55 am
Hi
I have a group of users who need to be able to create databases and tables, then import, export and manipulate the data in those tables. I'd like to be able to give them a set of permissions which won't constantly need updating so they need to be server level and I refuse to give the SA or SecAdmin rights.
I thought about giving them dbcreator but that only allows them to create or delete the databases. It does make the user who creates it the owner which allows him to update, insert, etc., but the other members of the group would have no rights.
Any ideas?
Alex
January 27, 2017 at 7:03 am
The database owner should be able to assign permissions in the database to other users. If you prefer a more automated solution, have you tried creating a database trigger that adds the group of users to the databases. I've never tried that second one, so I can't swear it'll work.
John
January 27, 2017 at 7:09 am
alex.palmer - Friday, January 27, 2017 6:55 AMHiI have a group of users who need to be able to create databases and tables, then import, export and manipulate the data in those tables. I'd like to be able to give them a set of permissions which won't constantly need updating so they need to be server level and I refuse to give the SA or SecAdmin rights.
I thought about giving them dbcreator but that only allows them to create or delete the databases. It does make the user who creates it the owner which allows him to update, insert, etc., but the other members of the group would have no rights.
Any ideas?
Alex
This sounds like a job for the model database - set up the permissions in there, and they'll be propagated across to any database created on that server.
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 27, 2017 at 7:19 am
The dbcreator role does provide a login with db_owner user permissins on databases they create. Note, however, that it won't give db_owner to other Logins with the dbcreator server role.
Model might solve the problem, but what if someone else creates a database that they should not have access to? You would need to ensure that their permissions are dropped.
Should they be sharing databases, or will they only be using their own? If they need to access others could they ask the person that created it access? Not saying Thomas' solution is wrong, just I'm not a massive fan of adding a bunch of users to the db_owner role on the model database.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 27, 2017 at 7:26 am
Thom A - Friday, January 27, 2017 7:19 AMThe dbcreator role does provide a login with db_owner user permissins on databases they create. Note, however, that it won't give db_owner to other Logins with the dbcreator server role.Model might solve the problem, but what if someone else creates a database that they should not have access to? You would need to ensure that their permissions are dropped.
Should they be sharing databases, or will they only be using their own? If they need to access others could they ask the person that created it access? Not saying Thomas' solution is wrong, just I'm not a massive fan of adding a bunch of users to the db_owner role on the model database.
Thanks for the replies guys.
TBH I'm not a massive fan of users being able to do this stuff full stop, but we don't always get to make the rules 🙁
The permissioning the model database sounds promising, as the group will all require access to each others databases.
I'll look into that
January 27, 2017 at 7:40 am
A problem with using dbcreator is that it allows people that have that role to drop databases, even if they weren't the ones who created them. Maybe you could just do:GRANT CREATE ANY DATABASE TO ...
if they only have this privilege, they can only drop databases they create as opposed to any database.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply