I often get the following question of my customers “Can you give me a list of all the users that can connect to our databases”. Actually, the customer wanted to have a list of users per database. To be complete, I added also the list of the Server Logins.
I created the following queries to answer that question.
First query will get all the server logins and the assigned server role(s). The logins that only have the public server role are also included in the list.
select sp2.name as [Login], ISNULL(sp.name,”public”) as Serverrole
from sys.server_role_members srm
join sys.server_principals sp on sp.principal_id=srm.role_principal_id and sp.type=”R”
right outer join sys.server_principals sp2 on srm.member_principal_id=sp2.principal_id
where sp2.type in (”U”,’2014-03-28 19:35:36’G”,”S”) and sp2.is_disabled=0
order by 1
The second query will get all the database users for each database and the assigned database roles. The users that are not a member of any role are also included in the result set.
create table #DatabaseUsers (
DECLARE @CMD VARCHAR(MAX)
SET @CMD = N”use [?]
select db_name() as [Database],ISNULL(rp.name,””No database role assigned””) as database_role, mp.name as database_user
from sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
right outer join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
join sys.database_permissions dp on mp.principal_id=dp.grantee_principal_id and dp.permission_name=””CONNECT””
Insert into #DatabaseUsers
exec sp_MSForEachDB @CMD
select * from #DatabaseUsers
drop table #DatabaseUsers
Remark: Disabled server logins and database users that are not allowed to connect to the database (REVOKE CONNECT) are not shown in the result set.