Restrict login to perform only specific actions

Question

I want to create a Login for a windows group which should only be allowed to do the following:

- Create new dbs

- Restore dbs from backup

- Delete dbs (only those, which were created by the login) - as "Delete backup and restory history information for database" is default checked in Delete Gui, there should be no error message when clicking OK.

- Create, delete and change data, schemas, etc. in these dbs

- Optional: View and change Properties of db

Is it possible to restrict a login in this way and how do I set it up?

Answer 1

it think you should be able to give the appropriate permissions to get what you want.

the only exception is the deletion of database which had been created by the same user. This can achieved either by a trigger or much easier by a stored procedure which check if the same user created the database and than delete it.

Answer 2

Create new database or restore from backups - the minimum permission required is db_creator server permission role.

delete dbs which were owned by login who created - as the login is owner of the database they shouldn't have any issues in dropping the database , but to remove data from msdb backup and restore tables the login should be mapped as a user in msdb database with write access

Create, delete and change data, schemas, etc. in these dbs / View and change Properties of db - Database owner can perform this

So the answer is try and make use of db_creator server role with additional rights to msdb database

---

Thanks, Leks

Answer 3

You may find this chart of all the permissions helpful.  http://go.microsoft.com/fwlink/?linkid=229142

 

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

Read my blog about the subject 

http://dimantdatabasesolutions.blogspot.com/2010/09/be-careful-to-grant-dbcreator-server.html

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

Read my blog about the subject 

http://dimantdatabasesolutions.blogspot.com/2010/09/be-careful-to-grant-dbcreator-server.html

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Was aware of that, thx anyway.

To implement the requirements listed in my first post I did the following:

- Created new database role in msdb with Execute Grant to SP sp_delete_database_backuphistory.

- Created new login with the windows group

• User mapping to msdb, database role membership to new database role
• Securable to server - Create any Database / Grant.
• Added new login to database role in msdb under Role Members.

Works for me.