query to get permissions for a user on all databases

  • I want to monitor rights change on certain users with windows authentication and sql server authentication. I am planning to automate this task by running queries against catalog views and saving the results in tables and generating email alerts if any permissions changed for any user on any database.

    example:

    I have these five windows users (Wuser1, Wuser2, Wuser3, Wuser4, Wuser5) that are part of a group called MyWUsers. They are set to have membership = public, Server roles = public and User Mapping = public to certain databases.

    Another user AdUser (user type =SQL Server) has membership and Server roles set to public and sysadmin, User mapping set to db_owner and public on certain databases.

    A service account (user type = windows) has membership and server role set to public and user mapping set to db_owner and public on certain databases.

    Please provide a sample query that I can use to get their memberships, server roles, use mapping, default schema on all databases on db instance on sql server 2008.

    Thank you.

    Forum User:cool:

  • Hi,

    I suggest another approach in SQL Server 2008.

    Please try using Audits. They are under Security in SQL Serfver Management Studio. It takes just a few minutes to create. Then you can script them as you can script any SQL Server object

    Create a New Audit, Give it a location, accept the default name or give it any other name.

    On a server or database level create a new Server or Database Audit Specification.

    Select Audit Action Type. As an example I have selected

    Database_Role_Member_Change_Group

    Database_Object_Permission_Change

    Database_Principal_Change_Group

    Then I have enebled Audit and Audit Specification by right-clicking and selecting Enable.

    As I test I have added a user to Master database and thenI have made him and then removed him from db_datawriter role. This has been recorded in a log. To view log, righ-click the Audit and selct View Audit Logs.

    I have got something like this:

    Date10/1/2013 5:57:21 PM

    LogAudit Collection (Audit-20131001-135447)

    Event Time 17:57:21.7858782

    Server Instance Name<my server instance name>

    Action IDDROP MEMBER

    Class TypeROLE

    Sequence Number1

    SucceededTrue

    Permission Bit Mask0

    Column PermissionFalse

    Session ID66

    Server Principal ID272

    Database Principal ID1

    Target Server Principal ID0

    Target Database Principal ID0

    Object ID1

    Session Server Principal Name<my domain name>

    Server Principal Name<my domain name>

    Server Principal SID0x150000052100019818780122381183047672310502264300

    Database Principal Namedbo

    Target Server Principal Name<the user name for the person permissions changed>

    Target Server Principal SID0x16123417675321371476413335254015494208143

    Target Database Principal Name<the user name for the person permissions changed>

    Database Namemaster

    Schema Name

    Object Namedb_datawriter

    StatementEXEC sp_droprolemember N'db_datawriter', N'<user name>'

    Additional Information

    File Name<file path i have specified when creating the audit>.sqlaudit

    File Offset1024

    Message

    Regards,Yelena Varsha

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply