Modifying Server Roles in SQL Server 2008

Question

Hi guys,

I just set up my company's first SQL Server 2008 (x86) installation on Windows Server 2008. I currently have an Active Directory group for people who are supposed to be able to log in, see all the databases on my server, and be able to create/delete/modify tables, functions, stored procedures for a database, but not be able to modify, create, or delete databases themselves.

Is there a server role for this that I'm not aware of, or do I need to make my own? If I have to make my own, how do I go about that? I've not seen a server role that would fill this need, but I could be missing something.

Thanks for your responses, I'm still relatively new at this.

Answer 1

Hi ,

If you need create / modify / restore db at the server level you need to add them to the DBCREATOR server role.
First create them a new sql server login with their domain account (using windows authentication) and assign them to any of the fixed server roles , sysadmin server role has the highest privilges among all the others.

Have a look at the availabe sql server roles,
http://msdn.microsoft.com/en-us/library/ms175892.aspx
http://msdn.microsoft.com/en-us/library/ms188659.aspx

If you need to delete database - the user should be under the DB_owner role at the database level.
You can create a new role at the db level ,but not a SERVER ROLE.

---

Thanks, Leks

Answer 2

To add to Lekss's reply: If you grant someone db_creator and they do create a db they will be dbo on that database.
I strongly advise against granting non-sysadmins db_creator unless you're actually assisting with the setup though. You get databases in the wrong locations, wrong sizes, wrong owners, wrong permissions, etc, etc, etc. It'll just be more work for you/your dbas.

Also- it doesn't seem to answer your question as you want users "to be able to log in, see all the databases on my server, and be able to create/delete/modify tables, functions, stored procedures for a database, but not be able to modify, create, or delete databases themselves. "
Granting db_creator will give them the ability to create and delete databases, so you don't want to do that.
Sadly, you can't create your own custom server level roles (hint hint MS ;)) but you can certainly grant them most of those permissions at the database level.
You may want to create a custom role with a script you can deploy easily (or even automate via a job) that grants the permissions you want. Or you can use the default roles db_reader, db_writer, and db_ddladmin. That will allow them to do pretty much everything you mention above with the exception that they can't modify security. So if someone creates a new sp they won't be able to grant access to that sp to anyone including themselves. Ditto for tables and other objects. This can be a problem and the only way around that is to grant db_securityadmin which in my opinion is a bit of a risk.
I tend to stop short of granting dbo to users in production environments because I don't want them to have the ability to backup ro restore their database and potentially break our backup chains (aka restore plans). There's also various db commands that I don't want to risk them mucking about with DBCC commands or some of the more intense (aka DETAILED) index statisitcs commands.