| Author |
Messages |
|
ghoover (guest)
 |
| 07/27/2006 9:19 AM |
 Quote
 Reply
 Alert
|
Due to the number of staff in our IT department, and due to SOX compliance, I need to be able to remove Domain Admins from being able to look at or alter data in a database, but I need them to be able to backup and restore a database.
How do I do this? |
|
|
|
|
mulhall (guest)
|
| 08/08/2006 4:00 AM |
Quote
Reply
Alert
|
That is a non-sensical requirement, nevertheless you could encrypt the data and grant them membership of db_backupoperator and DBCREATOR.
The fact is that a person who has full control over the domain, has full control over the servers implicitly even if not explicitly.
Which versions of SQL Server are you trying to secure? |
|
|
|
|
knavish (guest)
|
| 08/30/2006 3:21 PM |
Quote
Reply
Alert
|
The way that I accomplished this same thing for SOX compliance was to remove the BUILTIN\ADMINISTRATOR group from all SQL Servers and (now here's one I have only implemented briefly) to create SQL Login (SQL server or AD account, doesn't matter) that ONLY has the ability to BACKUP a database.
There are a couple of articles that will walk you through removing the BUILTIN\ADMINISTRATOR group. By doing this you will be keeping Domain Admins from having SystemAdmin privileges.
Rational for Removing BUILTIN\ADMINISTRATOR account from SQL Logins:
http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritysecurityadmins.asp
How to impede Windows Admins from accessing SQL Server (more on BUILTIN\ADMIN removal):
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q263/7/12.ASP&NoWebContent=1
As for the BACKUP only user, create your user, or add a AD Account, and grant it db_backupoperator ONLY in each, and every, database you want to backup.
From MS Website:
db_backupoperator Designed for users who need to back up a database. Members of this role can perform these tasks on the selected database: BACKUP DATABASE, BACKUP LOG, CHECKPOINT, DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB, DBCC TEXTALL, DBCC TEXTALLOC, and DBCC UPDATEUSAGE.
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c05ppcsq.mspx#ELFAE
Hope this helps;
Knavish
|
|
|
|
|
|