Solved

Setting SQL permission for a user to access databases they create

Posted on 2016-10-07
8
47 Views
Last Modified: 2016-10-17
Hello,
We have SQL server 2008 R2 and 12 databases under a single instance. I am trying to grant a user permission so that he :

1. Has full access to 3 of the existing databases including restoring and overwriting them
2. Is able to create new databases and have full access to them
3. Does not have unnecessary rights to other existing databases

What is the best way to accomplish this?

I understand that the dbcreator server role will grant the permission to create databases but it also allows the user to ALTER/DELETE all databases. Because of the third point above, I opted not to use this dbcreator role. Instead I used GRANT CREATE ANY DATABASE command so that he can create databases and only ALTER/DELETE the databases that he owns.

The user is able to create a new database, he is also create a new database by restoring from a backup file. However, he is not able to access them. He tries to access them via SSMS and he sees the error "The server principal loginname is not able to access the database DB under the current security context. Error 916."

I would appreciate any input.

Akira
0
Comment
Question by:realtimer
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41834097
db_owner role will work. Also check pros and cons of the same,
0
 

Author Comment

by:realtimer
ID: 41834102
Thanks but I cannot give db_owner role to databases that are not created yet. This user is testing a program and is restoring databases multiple times. And I cannot keep giving db_owner role to each database every time he creates it.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 41834103
It's not possible to allow them to create databases as you need server level permissions at that point.  Once you have server level permissions, you can effect any DB you want.  Best bet is to create the DB for them, set them as DBO, and they should be good to go from there.

If the user is testing a program . . . create another instance maybe?  This way you can give them SA while they test and when they're ready can move other to the main instance.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:realtimer
ID: 41834131
Thanks Kyle. CREATE ANY DATABASE is a server level permission. And he is able to create databases. If the user is able to create a database by restoring from a backup file, shouldn't he have access to the database he created by default?
We have suggested doing this test in an test environment but unfortunately, the program needs access to multiple production databases and they do not want to spend the time to set it up.
They need this right away but they also do not want to user to have too much permission.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 41834144
Understood about needing it on the production servers.  My statement wasn't that he wouldn't have access, but he could gain access (once you have server level permissions) to the other DBs on that server.

Regarding another instance.  What I'm suggesting there is installing a seperate SQL instance on the same box as the production server.  

(EG:  If you're machine is running the default instance of MACHINE_NAME . . .create a MACHINE_NAME\Testing instance).

This would  keep him out of the main server while he was testing.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41834303
Thanks but I cannot give db_owner role to databases that are not created yet.

Actually, you can.  Add the user as a db_owner in the model db.  All new dbs are created using model as a shell.

If you create new databases, and don't want the user to have access, just remove that permission.
0
 

Author Comment

by:realtimer
ID: 41834443
Thanks ScottPletcher. I just tested it but it didn't work when creating a new database by restoring from a file. If I create a brandnew blank database, then I have db_owner permission regardless of whether or not I am a db_owner of model database.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41835803
Yeah, quite right, the permissions get inherited from the original db.  Sorry for my mix-up there.

You could create a trigger on "CREATE DATABASE" to add permissions, something like below (you might want the code to check first if the user already exists before creating it, or to skip certain database name patterns, etc.).  I assume this trigger would fire after a CREATE from a file attach, but naturally you'd have to test it to be sure:

USE master
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER Create_Database_Trigger
ON ALL SERVER
AFTER CREATE_DATABASE
AS
SET NOCOUNT ON;
DECLARE @database_name nvarchar(128);
DECLARE @sql nvarchar(4000);
SET @database_name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(128)');
SET @sql = 'USE [' + @database_name + ']; ' +
    'CREATE USER [your_user_name] FROM LOGIN [your_user_name]; ' +
    'EXEC sp_addrolemember ''db_owner'', ''your_user_name''; '
EXEC(@sql)
GO
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question