Hi,

I was trying to get a list of all users that have the access to the
particular SQL Server installces and their permissions + those user's
permissions on each of the databases.
This is what I got so far:
SELECT srprin.NAME, srprin.type_desc, srperm.class_desc,
srperm.permission_name
FROM sys.server_principals srprin INNER JOIN sys.server_permissions
srperm ON srprin.principal_id = srperm.grantee_principal_id

However, it doesn't show me user's mappings (rights on each
database). What else am I missing?

T.

Did you check sp_helprotect?

--
-----------------------------
"Caminar sobre el agua y desarrollar software a partir de unas
especificaciones es fácil, si ambas están congeladas."
Edward V. Berard, ingeniero informático

http://blogs.solidq.com/es/elrincondeldba


"tolcis" <nytollydba (AT) gmail (DOT) com> wrote

That information is stored *inside* each database
(sys.database_principals), so you can't retrieve it with one single
SELECT statement. You can write your own cursor to loop over each
database, or possibly use sp_MSForeachdatabase (not documented - use
at own risk), or some other mechanism.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"tolcis" <nytollydba (AT) gmail (DOT) com> wrote

tolcis,

One additional hint. The sp_helplogins stored procedure will show a login
and its user roles on all databases to which it has access. This is often
useful and IF you only grant rights to database roles it may give you enough
detail. But, if you need more then Tibor has described what you should do.

FWIW,
RLF

"tolcis" <nytollydba (AT) gmail (DOT) com> wrote