Determining if Login is mapped to a specific database

I am new to SQL Server 2008, so if this does not make sense at first, I will try to edit with clarification.

Problem:I am trying to write a query that retrieves a list of all Logins that are currently mapped to a specified database.

What I have tried:I am writing an application that lists all master Logins, what permissions they have, and if they are mapped to my specified database. I have listed their permissions by using the sys.server_permissions table. I have found that sys.server_principals shows the Login's default database name. However, it does not show what databases the Login is mapped to.

2 Answers

The database users can be found in sys.database_principals. This will show you the database users and the logins they're mapped to.

SELECT *
FROM sys.server_principals sp
INNER JOIN [your database].sys.database_principals dp ON sp.sid = dp.sid

Database permissions are stored in sys.database_permissions. A role is also a principle. You can find which roles a user belongs to in sys.database_role_members.

USE [your database];
SELECT *
FROM sys.database_principals usr
INNER JOIN sys.database_role_members usr_roles ON usr.principal_id = usr_roles.member_principal_id
INNER JOIN sys.database_principals roles ON usr_roles.role_principal_id = roles.principal_id

The tables/views that start with database_ return data from the current database context.

Edit: fixed spelling of "principals"

You need a bunch of tables to do this - start with this:

SELECT [UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = NULL,
[PermissionState] = perm.[state_desc],
[PermissionType] = perm.[permission_name],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM --database user
sys.database_principals princ
LEFT JOIN --Login accounts
sys.server_principals ulogin
ON princ.[sid] = ulogin.[sid]
LEFT JOIN --Permissions
sys.database_permissions perm
ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN --Table columns
sys.columns col
ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj
ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas schem
ON schem.[schema_id] = perm.[major_id]
LEFT JOIN sys.database_principals imp
ON imp.[principal_id] = perm.[major_id]
WHERE princ.[type] IN ('S', 'U', 'G')
AND -- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY
ulogin.[name],
[UserType],
[DatabaseUserName],
[Role],
[PermissionState],
[PermissionType],
[ObjectType],
[ObjectName],
[ColumnName] 

taken from