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

  • I think you can do it by using User mapping while creating a new user login and assign db_datareaderrole for the database you want that user to allow only for reading data and for other database you can use only public option or else use role as data_denydatawriter
    – NetStarter
    Apr 12 '13 at 14:33
  • 1
    @NetStarter, that leaves out the most important part of the OP's specifications: "create and manage their own databases." Apr 12 '13 at 14:39
  • yes got you he cant create or manage own database thanks.
    – NetStarter
    Apr 12 '13 at 14:43

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.


Furthermore, dbcreator can alter and drop any database on the server. (Might be version dependent, checked on 2012.)

If you really want the users to be able to create their own databases you probably need to look into the explicit create database permission.

  • A login trigger can fix that problem. Though my preference would be to grant CREATE DATABASE.
    – mrdenny
    Apr 12 '13 at 17:53

There's a server role called DBCreator. If you create your login with that Server Role I bet that'll do the trick. Although I've never used it myself.

  • Unfortunately DBCreator won't work in this case. DBCreator can create, alter, drop, and restore any database. Tks for the effort though.
    – Sam S.
    Apr 12 '13 at 15:11
  • I don't recommend that. Straight out of BOL: "Members of the dbcreator fixed server role can create, alter, drop, and restore any database." It doesn't look like the OP is wanting that login to have those extensive permissions. Apr 12 '13 at 15:12
  • @SamS. How about granting CREATE ANY DATABASE to the login? Apr 12 '13 at 15:12
  • That's what I actually just did. I am going to test it on the user to see if "Create Any Database" will work. Thanks!
    – Sam S.
    Apr 12 '13 at 15:23
  • Good shout. I should've checked that. Apr 12 '13 at 15:25

If the CREATE ANY DATABASE permission (mentioned in a comment above) isn't fine grained enough or your paranoia level is high then I wouldn't recommend that approach.

Personally, I avoid granting permissions to users if at all possible. It's much easier to audit Sql Server security if users have no special permissions, only role memberships. In the cases where a user does require elevated permissions, I recommend using a certificate signed stored procedure. When a signed sp is executed the user inherits permissions from the cert. (See http://msdn.microsoft.com/en-us/library/bb669102.aspx for details.)

--Skeleton of a "secure" Stored Procedure to create databases
CREATE PROC CreateNewDatabase (@db_name sysname) 


--Validate database name starts with "UserDB_"
if @db_name not like 'UserDB[_]%'

--Create database, with known settings for recovery, filesize, growth, containment, etc.
declare @sql nvarchar(4000)
set @sql = N'CREATE DATABASE ' + .....
exec sp_Executesql @sql

--Grant permissions to the login

--Add DB to Maintenance plans and backup jobs, take full backup to start log chain.

--Send notification to DBA that a new DB has been created

--Etc, etc, etc.


Likewise, a different stored procedure could take care of dropping the DB when they are done with it. It could perform any required cleanup tasks like saving a final full backup to an archive folder or sending notification emails.

This is a good approach when you want to delegate a very specific task to a non-dba, for example, to allow the helpdesk to reset passwords for a subset of sql logins.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.