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]