Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Setting SQL permission for a user to access databases they create

Posted on 2016-10-07
8
Medium Priority
?
52 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 35

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 41

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 41

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 70

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

971 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