Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 58
  • Last Modified:

Setting SQL permission for a user to access databases they create

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
realtimer
Asked:
realtimer
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Pawan KumarDatabase ExpertCommented:
db_owner role will work. Also check pros and cons of the same,
0
 
realtimerAuthor Commented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
realtimerAuthor Commented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
realtimerAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now