Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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." –  Jeff Rosenberg Apr 12 '13 at 14:39
    
yes got you he cant create or manage own database thanks. –  NetStarter Apr 12 '13 at 14:43

4 Answers 4

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.

share|improve this answer

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.

share|improve this answer
    
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.

share|improve this answer
    
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. –  Thomas Stringer Apr 12 '13 at 15:12
    
@SamS. How about granting CREATE ANY DATABASE to the login? –  Thomas Stringer 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. –  Simon Osborne 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) 
BEGIN

SET NOCOUNT ON

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

--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.
...

END

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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