Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auditing Logins and Their Access Expand / Collapse
Author
Message
Posted Monday, October 01, 2007 8:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:04 PM
Points: 311, Visits: 1,080
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



Post #405035
Posted Monday, October 01, 2007 9:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
Try looking at fn_my_permissions or query sys.database_permissions or sys.server_permissions.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #405382
Posted Tuesday, October 02, 2007 6:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:04 PM
Points: 311, Visits: 1,080
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!



Post #405584
Posted Thursday, May 27, 2010 1:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 9:00 AM
Points: 91, Visits: 298
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
Post #929234
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse