SQL Server Performance Forum – Threads Archive
Security and permissions
I there a way we can monitor a SQL server and track every user activity including select statements generated. I am looking to do this for long term, so SQL profiler won’t fit in. Actually we have a vendor who do maintenance for our servers, however we have few servers with very confidential data and these vendors have sysadmin rights, as they have to do maintenance like backups, monitor server logs, errors, reindexing, dbcc checks, jobs, sql server agent etc. We dont want them to look at data?? If I have to give them same rights with granting him permissions to view data, what role or permission should I be granting to them. Though we can revoke sysadmin rights if we can create other database role, from which they can monitor servers and do maintenance as well. What kind of roles or permission cn i provide other than sysadmin and how can I make sure that they don’t view the data?? Also if possible can I keep a log of users and queries, who are trying to acess data??See thishttp://sqljunkies.com/WebLog/ktegels/archive/2006/11/09/25306.aspx can help you. If you don’t want them to view the data then only way is not to give access to the server or database, in this case the least permission is DB_DATAREADER where it will allow the users to view data and not to change any of the database objects. Other than this you can take help of above blog to implement, refer to the books online (updated feb2007) for more information on security auditing. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>