November 19, 2015 at 1:12 pm
Hello,
Our production database server is shared with a few organizations and we have various domain accounts that have "sys admin" privledges and other people that have direct connections to the databases.
For my specific group's database I would like to lock down the database and prevent any sort of DROP, CREATE or ALTER actions against the database except for my application logins and the DBAs.
What is the best practice method to do this and is it possible? I'm assuming it would need to be done with some sort of database trigger?
November 19, 2015 at 1:14 pm
Start by revoking unnecessary sysadmin permissions. If someone's got sysadmin, you can't stop them from doing something to your DB. You can slow them down (eg DDL triggers), nothing more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2015 at 1:22 pm
I can't revoke the permissions. Our servers are managed by the government and people just have SA rights. I also cannot change permissions of users in the other organizations. But I was instructed that I could institute some measures to keep people from removing objects etc...
But I suppose I can create a database trigger to specifically list tables that I do not want dropped at least?
November 20, 2015 at 2:11 am
You could but as the people have sysadmin rights they could just disable the trigger then remove the object.
As Gail stated, you can't stop people with sysadmin doing anything, you can only slow them down
November 20, 2015 at 5:13 am
Well gotta do what you gotta do. 🙂 Nobody is using the 'sa' account but these are people with dbo and or actual network administrators. Most of the people I'm trying to stop wouldn't know how to disable a trigger anyways. 🙂
November 20, 2015 at 5:26 am
Don't have to be using the SA account could have sysadmin rights against their login.
Plus plenty of info on the web on disabling DDL triggers, so if they really wanted to drop it they would only have to Google.
But as you say gotta do what you gotta do.
The only thing I would say is to put some auditing on, or continuously read the default trace for any actions just in case they do disable your trigger, at least you know who did it.
November 20, 2015 at 10:42 am
I know they just need sysadmin... was just saying we don't use the sa account.
I already have some server audits going on for certain things... but is it possible to capture disabling of triggers in a server audit or would I have to create something special for that?
November 20, 2015 at 10:56 am
amy26 (11/20/2015)
I know they just need sysadmin... was just saying we don't use the sa account.I already have some server audits going on for certain things... but is it possible to capture disabling of triggers in a server audit or would I have to create something special for that?
Sounds like a case where their permissions need to "accidentally" get reset and then selective readded as they complain about what they can't do.
November 20, 2015 at 10:58 am
LOL I would get fired if I did that. 🙂
Does one of the server audit specifications or db audit specs allow for tracking of things like dropping triggers/tables?
November 20, 2015 at 6:36 pm
amy26 (11/20/2015)
LOL I would get fired if I did that. 🙂Does one of the server audit specifications or db audit specs allow for tracking of things like dropping triggers/tables?
What if somebody drops your database? 🙂
November 21, 2015 at 5:11 am
LOL - then I guess we're screwed. 😉
I backup the transaction log and the database quite often though.... so, not entirely catastrophic. 🙂
November 23, 2015 at 6:43 am
amy26 (11/20/2015)
but is it possible to capture disabling of triggers in a server audit or would I have to create something special for that?
You have to create something specific to track the disabling or dropping of objects, but here's a thought that you may not have considered.
Set up a job to track the stuff, then either push the data to a server instance none of these people have access to, or push it to a text file in a share none of them have access to. That way they can't alter the logs, you will have fulfilled your boss's wishes, plus you're complying with several federal auditing laws (assuming you're in the U.S.).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply