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 thedb_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 logindb_owner
rights in their database. You can then grant the logindb_datareader
in the databases that it doesn't/shouldn't own. This gives you better manageability along with accomplishing your stated goal.