question

maria1980 avatar image
maria1980 asked

Deny permission to domain\administrator to select database

How to Deny permission to domain\administrator to select database in sql 2005
permissions
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
I may be wrong about this, but I'm pretty sure the domain admin can bypass just about any security you put in place. The one thing you can do is disable the local system administrator privileges, but the domain admin can get around anything you can do. If the domain admin isn't trusted by the company, time to hire a new one.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
Chances are the Domain Admins are in the server's Local Administrators group and that group has sysadmin rights on your SQL Server instance.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You can try removing them from the sysadmin role at the server level and putting their domain group in the deny_datareader and deny_datawriter roles of the dB. This won't stop a truly energetic individual but it is a speed bump. You can also audit heavily and watch for them performing unacceptable actions.
1 Like 1 ·
Maybe i have to be more specific. If i want to deny permission to the domain\administrator to select a specific database. what do i have to do?
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
Grant and Blackhawk are right. It is indeed possible to deny permissions to their account, but it would largely be moot since they would have a variety of ways around it. The one way you could prevent an administrator from being able to meaningfully read the data is through encryption. But this creates some complications and some risks that the data may be lost if the keys are lost. As Grant said, if you cannot trust your administrator, get a new one. If you truly need to keep that administrator, but keep the data secure from them, you may want to look at setting up a server on a different domain on which they are not the administrator and store the data there. Of course, keep in mind that without encyrption, someone with some time with the hardware during which they are not observed is likely able to bypass most security anyway. (For one example of how, they could remove the harddrives, read them as an external on a system they do have admin on and copy over the base files for the database. These files could then be attached to a SQL Server they have full control over.)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.