SQL Server Performance Forum – Threads Archive
User and Privilage auditing
I have heard about db audit.I am wondering what is user and their privilege auditing.How to accompolish this.The scenerio of db server is 500db with all db have exactly 2 user.What will be the step by step to accomposhish this kind of auditing.Futher what will be the perfoemance issues that will be faced during this process.Can this auditing proicess be automated.Can any one help me with scripts.
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx
http://www.databasejournal.com/features/mssql/article.php/2243271 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I want to do the auditing of privilages provided to users.
Here is my scnerio:
I have a sqlserver with approx 400 user dbs.Each and every db have their own login.All db have only one user such that username=loginname.Any of these users dont have any server roles.All have database roles.
All these users have provided with read,write,execute permission on their respective db objects.Further all these dbs have 2 super logins "DBA" and "sa" which have "server administrator role".
Now I need to do a auditing of the all privilages so that no two users have same dbs,none of the users have any server roles,all these have necessary db roles,and so on.
You can do this with PROFILER/Trace but it is not that easy to go.
Then you must deploy third party tools to give you detailed information on auditing.
Check under tools section of this website for a review of audit tools. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
"Now I need to do a auditing of the all privilages so that no two users have same dbs,none of the users have any server roles,all these have necessary db roles,and so on." Having read through your whole post, if the above is the only end goal you can achieve this by running a script across all the databases to correlate sid to hasdbaccess, and return results where a particular sid has islogin and hasdbaccess = 1 in more than one db. Add in where roles = 0x00 from sysusers and you’ll find accounts that aren’t role members. You can reference syslogins to determine server role membership, or open them in EM – as these are server roles, this should be a cursory examination.
]]>