July 29, 2005 at 4:42 am
We have tables which we would like to prevent anyone from inadvertently updating, even the system administrators. Is there a way to do this? The sysadmin role appears to outrank any permissions or access we can grant or deny.
July 29, 2005 at 4:54 am
No, it is not possible to stop logins that are members of the server role sysadmin from anything really. As you noticed, sysadmins have every permission there is and it is not possible to remove it. However, you can revoke the right for the BUILTIN\Administrators account to login to SQL Server, if that would help you.
July 29, 2005 at 7:41 am
Also, within the database a sysadmin maps in as dbo, which means the sysadmin effectively ignores any security on a database object.
K. Brian Kelley
@kbriankelley
July 29, 2005 at 10:30 am
You can't stop SysAdmins, but you can slow them down. There was one table I didn't want anyone, particularly myself, from inadvertently messing up. I put a trigger on it that raised an error (with a descriptive message) when an invalid command was issued. While a SysAdmin (or db_owner) would drop or disable the trigger, they'd have to do so deliberately.
Philip
August 1, 2005 at 3:49 am
As other people have said, you cannot stop a sysadmin doing whatever they want.
However, you can prevent them reading data that is sensitive, and you can find out what changes they have made.
The best (only?) way to really protect sensitive data is to encrypt it. This will require third-party products in SQL200 or below, but is a standard part of SQL2005.
You can set up a profiler trace to capture all changes made to all databases by sysadmin accounts. The sysadmins should then be able to justify any changes made against work requests, etc. However, to implement this you need to move all application updates to named accounts and not use SA authority, or you will not gain anything useful.
Do doubt other people will suggest other ways to monitor what sysadmin users do...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 1, 2005 at 4:05 am
It sounds to me as if too many people have access to sysadmin, just look at it this way, sysadmin on a SQL Server is effectively GOD, do you really want to make that many people GODS?
I know that it's often easier to give everyone full access but it's not the way that anyone serious about security would every go, make the DBA sysadmin (he'd leave if you didn't) and make sure that they're properly qualified/experienced enough.
You need to trust someone in this world, good DBA's know that they don't mess with the contents of tables (they're too busy fixing things like slow queries); if they do then they're not as good as they say they are. You need to implement proper change control procedures and more importantly start trusting properly qualified and experienced people will act in a professional manner.
Sorry to jump on my soapbox but security is a subject close to my heart (as it is for most DBA's), if you can't trust someone not to make an unauthorised change then don't let them.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply