Wednesday, August 3, 2011

SQL Server Security Audit


This content is moved to http://www.sqlservergeeks.com/blogs/manu0417/personal/730/sql-server-security-audit-a-time-consuming-task-for-dba-simplified

-Manu

3 comments:

  1. Hi,

    Security audit in SQL server captures only successful and failed logins. It does not capture the application that uses the login. Login used by an application may have been full read and write access on all the tables and procedures, but the application restricts the users by providing a front-end which will allow them to see only a few columns, tables, etc. Thanks...

    ReplyDelete
  2. Hi Manu,

    This is how the security is designed in the Sql server.

    > Windows Group is created and added it as part of Login (SO that any user part of the Group can login to the serve and get all the privlages given to teh Windows Group)

    my challenge is : I need to find out the Windows Group to which the User Who Logged in belong to.

    Please need your help on this.

    ReplyDelete
  3. Hi,

    The Security of SQL Server works differently with Windows authentication. The login fetches the token from Windows and that token is validated to authenticate into SQL. So if the group to which the login belongs have access to SQL it will provide access to SQL. But I am not sure if we can get the group name from SQL to which the login belongs. But we can use windows cmd shell to get the groups to which the user belongs and check which group has access to SQL. I can help you write a query or SP to help you with that.

    Regargds,
    Manu

    ReplyDelete