October 21, 2008 at 1:32 pm
We have 3 DBA's that have unlimited access to SQL Server Management Studio (part of the builtin\administrators). Due to SOX (Sarbaines Oxley) we need to restrict permissions to certain DB's (i.e. either read only access or no access to tables, stored procedures, etc). What is the best way to restrict access to a DB through SQL Management Studio while allowing access to other DB's. Thanks for the help in advance.
October 21, 2008 at 2:31 pm
Is Builtin\Admininstrators a member of the Sysadmin server role? If you want to restrict the permissions of the DBAs in particular databases, you'll have to remove Builtin\Administrators from Sysadmin and grant the allowed permissions in each database. I recommend using database roles, either fixed or custom, as much as you can.
As an aside, it seems kind of strange to me that a DBA wouldn't have complete administrative access in SQL Server. But my workplace isn't subject to SOX regulations, so maybe that's just what you all have to deal with.
Greg
October 21, 2008 at 2:52 pm
Greg,
Thanks for the reply and I will try your solution.
Yes it does suck that we cannot have exclusive rights to the databases. The main DB of concern is a payroll DB. They do not want us to be able to see any information or change any information directly in the tables. For special projects we would be granted access on a temporary basis. So far we have not lost access but it is coming. Welcome to SOX...
October 23, 2008 at 9:19 am
Or isolate payroll on a separate instance of SQL on the same server. But then you'll need to buy another SQL license.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply