SQL Logins

[Len Svitenko]

How can I get a list of all SQL server logins that are on a given sql
server? What sql query would get me that? Or is there an sp that
gives me that. Also, if it could list what db they have rights to
that would be great also.

[Raghuraman]

Re: SQL Logins

Hi, Jpmc

As far the enterprise manager is concerned , it is almost a open book.
Any db cann be backed up & restored and the E.MANAGER might be
designed to view all the databases by any body. so only the log ins
are invisible when u restore the db.

but the db is in active when u try to open a db thru query analyser in
diff login which does not have the right to access the db.

The thing is u can work with any databases in E. manager .

i think u would be cleared .


With THANKS RAGHU

[Shervin]

Re: SQL Logins

Len,

For a list of logins and their server roles check master..syslogi ns table.
To see if a login has access to a DB check DB..sysusers. syslogins table
gives you SID of each login, if this SID appears on a sysusers table in a DB
it means that user has access to this database.
To check user roles use sp_helprole and sp_helprolememb er procedures.

In general, if you are looking for some kind of information that Enterprise
Manager can provide, simply run Profiler and go get the information using
EM, then check Profiler's log to get an idea how to do the same in your
program. This at least gives you some SP names to look up in Books Online.

Good luck,
Shervin



"Len Svitenko" <its_goodtobeth e_king@yahoo.co m> wrote in message
news:6092a26a.0 310031640.19a46 f03@posting.goo gle.com...[color=blue]
> How can I get a list of all SQL server logins that are on a given sql
> server? What sql query would get me that? Or is there an sp that
> gives me that. Also, if it could list what db they have rights to
> that would be great also.[/color]