Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Setting SQL permission for a user to access databases they create

Posted on 2016-10-07
8
Medium Priority
?
51 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 30

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

688 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