Solved

Setting SQL permission for a user to access databases they create

Posted on 2016-10-07
8
37 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 17

Expert Comment

by:Pawan Kumar Khowal
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 39

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
 

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 39

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:ScottPletcher
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:
ScottPletcher 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 33
MS SQL Backup 24 70
t-sql complement 8 30
SSIS import multiple CSVs into associated tables 3 28
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now