Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am looking for a t-sql script which can list the databases and and the respective roles/privileges mapped for a particular user. Using SQL Server 2008 R2.

Regards Mohammed

share|improve this question
    
Have you tried any solution? This looks a bit like homework you request others to do. Have you tried to build a query using security catalog views? –  Marian Dec 12 '11 at 15:22
    
I am a DBA not a developer to break my head on coding ;-) If you wanna help, then please help me out! Thanks! :) –  Mohammed Dec 13 '11 at 8:46

1 Answer 1

Within each database, you should be able to do something like:

execute as login = 'SomeLogin';

select *
from sys.database_principals
where IS_MEMBER(name) = 1;

revert;

And you should be able to look in sys.database_permissions to see what has been gratned to each database_principal.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.