Listing rights/roles on MS SQL Server 2005 and Analysis Services using Perl (or PHP)

Question

As part of a project to create a tool to query all sorts of permissions relevant to users in our area at work, I need to query some information about our database servers. The servers run SQL Server 2005 (including instances of Analysis Services 2005). I need to be able to find for a given server:

• The list of logins on that server
• The list of roles on that server
• What each role has rights to
• Who is in each role (or for each login, what roles does it belong to - either way works)

The application is currently written in Perl, so if you know of techniques to use to query this information using Perl, I would be very thankful. The only other application language I can work with is PHP, but that is to be avoided for consistency reasons if at all possible.

Answer 1

I don't have much knowledge about SSAS, so I will leave that part of the question to someone with the appropriate experience.

For 1, 2, and 4, you should be able to use the system stored procs sp_helplogins, sp_helprole, and sp_helprolemember. You can also get role membership from the view sys.database_role_members. As far as what users/roles have access to, the view sys.database_permissions contains that data. No perl-specific methods are necessary, just your normal select and exec statements.

It might be worth a few minutes to read through the Books Online entries on system views, system tables, and system stored procs, which will return the data you want in various forms.