Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AJSpruit
Starting Member
3 Posts |
 Posted - 2013-05-15 : 07:41:37
|
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 databaseAuthenticate serverConnect SQLControl serverView any databaseDENY:Create any databaseAnd 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 RegardsAJ |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-05-15 : 19:23:13
|
| We had a similar problem which we resolved by adding a server level trigger:[CODE]CREATE TRIGGER [tr_dropdb]ON ALL SERVERFOR DROP_DATABASEASSET NOCOUNT ONDECLARE @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) ROLLBACKEND[/CODE]=================================================I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain) |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-15 : 19:29:12
|
| Ugh. You have a lot of work on your hands... |
|
|
|
AJSpruit
Starting Member
3 Posts |
Posted - 2013-05-16 : 08:57:35
|
@Bustaz KoolThank 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@russellThank you for you sympathy, making my way through Blitz script to get these in some kind of order  Kindest RegardsAJ |
|
|
|
|
|
|
SQL Top tier Security