A recent question on the forums asked how to determine if a database

user was a Windows user or group and what login did it correspond to?

By matching up the SID on the database user and sql server login

tables, you can find this information out. First, let's look at getting

information on the server login.

In SQL Server 2000, we can query a system table, syslogins, for that

information. However, there isn't a single field we can query to

determine if a server login is a SQL Server login, a Windows login, or

a Windows group. Therefore, we have to put together a nested CASE

statement to get the information back in a single field. Here's how:

SELECT

  sl.name [ServerLogin],

  CASE sl.isntgroup WHEN 1 THEN 'Windows Security Group'

  ELSE

    CASE sl.isntuser WHEN 1 THEN 'Windows Login'

    ELSE 'SQL Login' END

  END [LoginType]

FROM master.dbo.syslogins sl

ORDER BY [ServerLogin]

In SQL Server 2005 the catalog view sys.server_principals is what we're

after. It does contain the information on type in a single field,

[type] or [type_desc]. If we're only after logins and windows groups,

we can look at [type] to filter on. However, [type_desc] has the

written out type like windows_user. I've used both in my query:

SELECT

  sp.name [ServerLogin],

  sp.type_desc [LoginType]

FROM master.sys.server_principals sp

WHERE type IN ('G', 'S', 'U')

ORDER BY [ServerLogin]

How do we tie these to the database user? In SQL Server 2000 we'll have

to query against another system table, sysusers. If we're only

interested in the users corresponding to logins, an INNER JOIN against

syslogins will do it. This is necessary because the database roles are

also reported in sysusers, however, their SID values are NULL. We also

have to filter out dbo because dbo should map back to the server login

which actually owns the database. Guest also has a value for SID

(0x00), and while there shouldn't be a SID with a value I'd rather just

filter it out.

SELECT

  su.name [DatabaseUser],

  sl.name [ServerLogin],

  CASE sl.isntgroup WHEN 1 THEN 'Windows Security Group'

  ELSE

    CASE sl.isntuser WHEN 1 THEN 'Windows Login'

    ELSE 'SQL Login' END

  END [LoginType]

FROM master.dbo.syslogins sl

  JOIN dbo.sysusers su

    ON sl.sid = su.sid

WHERE su.name NOT IN ('dbo', 'guest')

ORDER BY [DatabaseUser]

SQL Server 2005 presents a catalog view for the database users as well

in sys.database_principals. Again, I can tie back to

sys.server_principals with the SID. And as with the SQL Server 2000

example, I'll filter out dbo and guest.

SELECT

  dp.name [DatabaseUser],

  sp.name [ServerLogin],

  sp.type_desc [LoginType]

FROM master.sys.server_principals sp

  JOIN sys.database_principals dp

    ON sp.sid = dp.sid

WHERE dp.type IN ('G', 'S', 'U')

  AND dp.name NOT IN ('dbo', 'guest')

ORDER BY [DatabaseUser]