Wednesday 14 November 2018

Show all users in all databases with role name, user type and enabled status

DECLARE @command varchar(1000)

create table #results (

 dbname nvarchar(400), rolename nvarchar(400) , UserType nvarchar(400), create_date datetime, modify_date datetime , authentication_type_desc nvarchar(400), is_disabled bit


)


SELECT @command = ' use [?];
SELECT ''?'' as dbname, d.name , d.type_desc, d.create_date, d.modify_date , d.authentication_type_desc , s.is_disabled
FROM sys.database_principals d
INNER JOIN sys.server_principals s ON s.sid = d.sid
LEFT outer JOIN sys.database_role_members r ON r.member_principal_id = d.principal_id

'
--select @command

insert into #results
EXEC sp_MSforeachdb @command

select * from #results

drop table #results