SQL Server Security Methodology | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server Security Methodology

My company is going through a large security crackdown, and making drastic changes to the way business is done within all databases environments. This is long overdue in my mind. However, I have to overcome serious political and system control issues in order to institute some of the standards our security department is calling for. Since many of the developers have been managing their own environments it is a major undertaking to eliminate some of their access while still granting them the rights they need to do their job. I was hoping to find someone who has implemented a very good methodology in regards to allowing lead developers to have access to schedule batch jobs and dts packages, without granting them dbo rights or syadmin access. My issue is that I do not want to have to manage these people’s scheduled jobs, but once I removed them from the sysadmin server role they could no longer access their jobs or dts packages. Is there an easy way to do this? Or a more complex way that you have designed or anyone you know of?
Any advice would be appreciated.
In general if those jobs are owned by those developres then they can execute/maintain their own jobs but not others. Also users need permission on msdb..sp_enum_sqlagent_subsystems and msdb..sp_get_sqlagent_properties to run their own jobs including setting ‘TargetServersRole’ in MSDB for the users. For information refer to this link http://vyaskn.tripod.com/sql_server_security_best_practices.htm] for security and this link http://vyaskn.tripod.com/sql_server_administration_best_practices.htm] for Admin. HTH
Satya SKJ

With the way SQL Server works, I think you might have to implement another layer (read a utility) of security to circumvent the problems you guys have brought upon yourselves. This utility would control access to various parts of the SQL server DBs but still perform the various tasks as dbo or sysadmin. Your specific security model would have to be handled by this layer. Unfortunately though,I don’t know of any such tool in the market (maybe somebody does). Developing one is a thought you might also entertain -Easier with SQLDMO out there. Nathan H.O.