Return all database roles for particular user

[corningIT]

How can I generate a list of unique roles for the current user from all databases on one server?

 

[mutley1]

You could run sp_helprolemember on each DB to get a list of the roles and their members & look for the user you're interested in.

 

[mutley1]

Sorry mate - I meant

sp_helplogins 'login you want to know about'

from master DB and will list all DB's and then wehre the record says "member of" that is the role in that DB it is a member of.

M.

 

[corningIT]

Ok, using sp_helplogins gets me a lot closer for what I was looking to do. Basically, I've written a front end that needs to check all of the existing dbases on the server to generate a list of what they should 'see.'

Reading through the sp_helplogins stored procedure though, I've noticed that only sysadmins can run this, and the stored procedure creates several temporary tables to store the table before returning the results.

Is there a way to structure this using only the sysdatabases for a dblist, and somehow looping through each database to determine the roles / users who have access?

 

[mutley1]

Sorry Corning,

Don't have the time to look at it in detail now (holiday!!! Sorry!!!) but if you take the SP apart, then guess you could insert the second half of the SP into a temp table and link to sysdatabases so it only returns system DBs then return all the values from the temp table. I'm back in 2 weeks, so if you still have probs, will get back to you then!

Cheers,

M.