SP Listing all users with access to a DB????

[ecka00]

i'm looking for an SP or any script that list all of the logins that have access to a specific Database. IMPORTANT. not all of the users listed in a database, but logins that have actual permission to a given database. We move are databases around and have several orphaned users with said database. I could care less about a user unless they exist in the master DB. -Allen

[skhanal]

You can query sysprotects table.

To get orphaned users you can use

sp_change_users_login 'report'

[ecka00]

thanks, for the reply. however, sysprotects does not list user logins nor does it list db names. probably need to join with other system tables.

also, not interested in orphaned users--looking for non-orphaned users with access to a specific database.

[skhanal]

Are you looking for this

select * from sysusers as su
join master..syslogins as sl
on su.sid = sl.sid

[ecka00]

that's the ticket--thank you!

[MAK]

sp_helpusers

[rmiao]

sp_helplogins