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