Post reply

Auditing Logins and Their Access

  • October 1, 2007 at 8:23 am

    #179479

    Hello Everyone,

    I'm looking for a script that will output a list of users/logins and all their rights and which databases they have access too. I'm trying to generate a list to provide to managers so that they can see who has access to what.

    I've used sp_helpuser and sp_logins but can't send the output to a table.

    We are using SQL 2000 and SQL 2005 servers.

    Thanks

    Rudy

  • October 1, 2007 at 9:39 pm

    #737995

    Try looking at fn_my_permissions or query sys.database_permissions or sys.server_permissions.

  • October 2, 2007 at 6:56 am

    #738117

    Thanks for the great information but not what I need. I guess I'm not explaining myself. Let see, Ok. I want to create a report like below

    User/Login Name DB Name Access Rights to DB

    John Doe testdb datareader

    A simple report like this would then be added to a table which would house all the servers (via linked server), all the databases and all the users.

    Next I would use SSRS and active directory to allow each servers manager/business owner to view the reports on their server quarterly. The person can then review and confirm who has access to the databases.

    I have everything else built expect extracting the data to a table. Right now I'm modifying the sp_helplogins to populate the tables.

    I can't believe that no one produces reports for the business to review. Once I complete this project I think I'll ask if I can create an article for this great website.

    Thanks again!

    Rudy

  • May 27, 2010 at 1:00 pm

    #1173540

    Here is a script I wrote to find out what logins were on my server(s) and what roles those logins belong to. It also will so if the user is a NT User or NT Group. hope it helps.

    SELECT

    LoginName,

    dbName DefaultDB,

    DenyLogin,

    HasAccess,

    SysAdmin,

    SecurityAdmin,

    ServerAdmin,

    SetupAdmin,

    ProcessAdmin,

    DiskAdmin,

    DbCreator,

    BulkAdmin,

    1 [Public],

    IsNtUser,

    IsNtGroup

    FROM sys.syslogins

    WHERE

    LoginName NOT LIKE '%Certificate%'

    ORDER BY

    DenyLogin,

    LoginName

    Greg Roberts

Viewing 4 posts - 1 through 3 (of 3 total)

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