get user list from system tables

On May 12, 8:46*pm, iccsi wrote:
> I would like to get server and databases user list.
> is it possible to have a query to get from system tables?
> If yes, can you please help me which system tables or system stored
> procedures can get those information?
>
> Your information and help is great appreciated,

select database_principals.name DatabaseName
,server_principals.name Servername
from sys.database_principals database_principals
join sys.server_principals server_principals on
database_principals.principal_id = server_principals.principal_id;

You will have to run this in each database.

Thanks for helping,
Is it for SQL Server 2000?
I tried to run it and get

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.database_principals'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.server_principals'.

Thanks again,


Tom wrote:
> On May 12, 8:46*pm, iccsi wrote:
> > I would like to get server and databases user list.
> > is it possible to have a query to get from system tables?
> > If yes, can you please help me which system tables or system stored
> > procedures can get those information?
> >
> > Your information and help is great appreciated,
>
> select database_principals.name DatabaseName
> ,server_principals.name Servername
> from sys.database_principals database_principals
> join sys.server_principals server_principals on
> database_principals.principal_id = server_principals.principal_id;
>
> You will have to run this in each database.

On May 13, 5:09*pm, Erland Sommarskog wrote:
> iccsi (inu...atgmaildotcom) writes:
> > I would like to get server and databases user list.
> > is it possible to have a query to get from system tables?
> > If yes, can you please help me which system tables or system stored
> > procedures can get those information?
>
> > Your information and help is great appreciated,
>
> The answer Tom gave you applies to SQL 2005. There was a radical change
> in how metadata is accessed in SQL 2005 compared to SQL 2000.
>
> Instead of giving you a query, I will just refer you to the T-SQL Reference
> in Books Online and the section System Tables. It's much better that you
> read in this at your own leisure than I spoonfeed you queries.
>
> There are also system procedures you can use, sp_helpxxx.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...atsommarskogdotse
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Took me awhile to recall SQL2000

select sysusers.name DatabaseUser
,syslogins.name SystemName
from dbo.sysusers sysusers
join master.dbo.syslogins syslogins on sysusers.sid = syslogins.sid

Again you have to run this in each database.

Tom