Finding all SQL users permissions and access on every server and DB?

Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.

Sorry if I didn't provide enough information on my first post. Listed below are the requirements of items I need to put in an excel sheet. Thanks!

I need to gather:

Who has admin access to what servers

What are the log in accounts and what is it used for in SQL

Which users have which rights to which databases

Thanks again.

|||

Are all servers running SQL Server 2005, or do you have a mix of versions? This list can get to be pretty long very easily. Anyway, assuming you have only SQL Server 2005 servers, here are some pointers:

1) For admin access, you can query members of sysadmin and, additionally, you can query grantees of CONTROL SERVER. Check the sys.server_role_members and sys.server_permissions catalogs.

2) For login accounts, check the sys.server_principals catalog. What they are used for in SQL is something that isn't known to the database - the semantics of use of a certain entity are not captured anywhere - only its capabilities are. So you can find out what those logins can do, but not necessarily what they are meant to do.

3) Database owners and sysadmins aside, you need to list the users from sys.database_principals. Their permissions are stored in sys.database_permissions. For permissions inherited from groups or roles, you have to do separate queries - not a trivial task.

I'm not sure how helpful this information will be to you, even if you go to the trouble of getting it - I expect you will get way more data than any single person will actually want to analyze. It would be simpler to think of what you want to get out of this information and search for that particular thing specifically.

Thanks

Laurentiu

|||

Thank you very much for the considerate answer, I'll look into those and my appologies for not mentioning we are only running SQL 2000 exclusively. We will upgrade to 2005 in about 1-3 months.

|||

Ok, on SQL Server 2000 you would do something similar, but you will need to use the old catalogs. I don't know their names by heart, I just remember sysprotects was storing the permissions, but you can search Books Online and it should contain this information. If you can't figure out how to query the old catalogs, let us know.

Thanks

Laurentiu

|||

Hi Laurentiu.

I have a big problem and I would like to ask you via e-mail. I cannot find your e-mail, so please write to me on gg@.moj.pl.

I hope you will contact me. Thank you very much.

Jack.

|||

Ok so this is what I have done so far:

SELECT DISTINCT syslogins.loginname, syslogins.dbname,
@.@.SERVERNAME AS 'ServerName'
FROM sysprotects, syslogins
WHERE sysadmin = 1

BUILTIN\Administrators master HMIS-MMCSPADDEN\JDELOCAL
sa master HMIS-MMCSPADDEN\JDELOCAL

I believe this is giving me the information for the users who can connect to our SQL server, but what about the database and table access rights? Where would I find those? Thanks in advance.

|||

Note that you can drop the sysprotects from your statement, as you are not selecting anything from it.

For database permissions, you can use syspermissions: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx. You can ignore sysprotects, as you can query syspermissions instead, but here is the link for it as well: http://msdn2.microsoft.com/en-us/library/aa260449(SQL.80).aspx

For database users, you can use sysusers: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx.

For role memberships in a database, you can check sysmembers: http://msdn2.microsoft.com/en-us/library/aa260440(SQL.80).aspx

The article for syslogins is here: http://msdn2.microsoft.com/en-us/library/aa260424(SQL.80).aspx.

And, to browse all system catalogs in SQL Server 2000, you can start from here: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx.

Thanks

Laurentiu

|||Wow thanks!