SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

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

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

DENY:
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
AJ

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1642 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]
ON ALL SERVER
FOR DROP_DATABASE
AS
SET NOCOUNT ON

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)
BEGIN
   RAISERROR('This operation is NOT allowed',0,1)
   ROLLBACK
END


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

russell
Pyro-ma-ni-yak

USA
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

AJSpruit
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

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

Kindest Regards
AJ
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.17 seconds. Powered By: Snitz Forums 2000