Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Top tier Security
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
3 Posts

Posted - 05/15/2013 :  07:41:37  Show Profile  Reply with Quote
Hi All

We currently run 3 SQL 2008R2 instances across 3 machines with about 250 Db's on each instance. As per usual we have a handful of users who feel that it okay to keep creating DB's on these instances so we are trying to prevent that unfortunitly they do need some level of access to the DB's to do various tasks (Queries,Backups,Restores and JDBC connections).

I took over these instances when our current DBA left so the initial setup was created and everyone in the organisation had SYSADMIN Server role...which i quickly removed...

I have set up 2 AD groups, one for Admins (currently a team of 6 members who have full SYSAdmin roles) and one for Everyone else. From what i have read the easiest way to control the access if via the the Mapping option (which would involve setting up the mapping everytime a db is created or restored, current around 15DB's\pay) so after spending a few hours boggeling at the SQL Security poster i found the by Granting and denying certain Securables i was able to control the access at a higher level.

So what i did (For the everyone group)

Alter any database
Authenticate server
Connect SQL
Control server
View any database

Create any database

And this works a treat to stop users creating Databases but still allowing them to take backups, however they aren't able to do restores and they are able to delete DB's which is why im here!

Does anyone know what securables i need to set to allow them to Backup\Restore\Query but not to Create\Delete?

User mapping is not a viable option as we don't have the hours to manage this.

Sorry for the long post....

If anyone can point me in the right direction it would be greatly appreciated.

Kindest Regards

Bustaz Kool
Flowing Fount of Yak Knowledge

1834 Posts

Posted - 05/15/2013 :  19:23:13  Show Profile  Reply with Quote
We had a similar problem which we resolved by adding a server level trigger:
CREATE TRIGGER [tr_dropdb]

DECLARE @UserID varchar(255)

select @UserID = SUSER_NAME()

IF @UserID NOT IN ('List of DBA Users')     -- <<< <<< <<< Modify this list with your User name and others (maybe)
   RAISERROR('This operation is NOT allowed',0,1)

I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)

Edited by - Bustaz Kool on 05/15/2013 19:24:19
Go to Top of Page


5072 Posts

Posted - 05/15/2013 :  19:29:12  Show Profile  Visit russell's Homepage  Reply with Quote
Ugh. You have a lot of work on your hands...
Go to Top of Page

Starting Member

United Kingdom
3 Posts

Posted - 05/16/2013 :  08:57:35  Show Profile  Reply with Quote
@Bustaz Kool
Thank you sooo much for the trigger script, that seems to have done the job. That is one pretty neat trick though i have also create on for the CREATE_DATABASE trigger so fingers crossed

Thank you for you sympathy, making my way through Blitz script to get these in some kind of order

Kindest Regards
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000