Post reply

The Complete SQL Server 2005 Permissions Report

  • May 10, 2010 at 6:38 am

    #1164930

    Excellent script, thanks! I was not seeing the object level perms for users tied to database roles I had created, so I simply added another OR statement to the where clause to get what I was looking for:

    SELECT DISTINCT Principal_Name,Login_Name, DatabaseName, ObjectName, ObjectType, PermissionName, state_desc, Grantor

    FROM @ObjectPermissions op

    WHERE ISNULL(Login_Name, '') like @loginName

    OR ISNULL(Principal_Name, '') like @loginName

    OR ISNULL(Principal_Name, '') in (SELECT DISTINCT DB_RoleMember

    FROM @DBRolePermissions dbrp

    WHERE (ISNULL(Login_Name, '') LIKE @loginName

    OR ISNULL(Principal_Name, '') LIKE @loginName)

    AND dbrp.DatabaseName = op.DatabaseName

    AND Permission_Type = 'DATABASE_ROLE')

    ORDER BY DatabaseName, Principal_Name, ObjectName, PermissionName

  • May 10, 2010 at 11:24 am

    #1165146

    Do you have an example of what output should look like ?

    thanks

    jim

  • May 10, 2010 at 12:43 pm

    #1165182

    Great script! I don't see synonyms, can that be added, and the "object level perms for users tied to database roles I had created" from above?

  • October 26, 2012 at 9:28 am

    #1553251

    Thank you so much for this script. Is there any equivalent script compatible with SQL Server 2000?

    Thanks for any help.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • November 10, 2016 at 3:09 pm

    #1911794

    webrunner (10/26/2012)

    Thank you so much for this script. Is there any equivalent script compatible with SQL Server 2000?

    Thanks for any help.

    - webrunner

    Just want to add an updated note that I am using still this script well past SQL 2000 🙂

    Thanks again - this script is so useful!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 5 posts - 16 through 19 (of 19 total)

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