Sql-server – the best permissions to set for only creating and managing own databases

sql serversql-server-2008-r2

I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of roles/permissions to use to implement this?

Thank you for your help!

p.s. I am using SQL Server 2008 r2

Best Answer

If you grant the dbcreator server role to a login, that login can create databases. Databases created by a login also (unless changed as part of the process) will be owned by that login, meaning that the login will be a member of the db_owner role for that database and have full rights to that database and all objects within it.

The primary issue here is that by granting the role, you will have no control over when and how the login creates databases, nor do you have any control on how many databases that login creates. A login with dbcreator can create as many databases as there is space for. My recommendation is that you create the database for the login and then grant that login db_owner rights in their database. You can then grant the login db_datareader in the databases that it doesn't/shouldn't own. This gives you better manageability along with accomplishing your stated goal.