April 26, 2012 at 7:30 am
Hi Team,
service account logins and corresponding users with roles and permissions on each database
Can you any body please provide script for the above
Thanks in advance.
Tx
April 26, 2012 at 7:59 am
Are you looking to report on that or to create that?
April 26, 2012 at 8:05 am
i m looking for the report
April 26, 2012 at 8:17 am
You may need to modify this to suit your purposes but here you go. This will get server level logins:
select [AccountName] = sp.name,
[AccountType] = type_desc,
[ServerRoles] = coalesce((
select sp2.name + ',' as 'data()' from sys.server_role_members srm
inner join sys.server_principals sp2 on srm.role_principal_id = sp2.principal_id
where sp.principal_id = srm.member_principal_id
for XML PATH('')
), '')
from sys.server_principals sp
where sp.type not in ('R','C')
And this will get DB level users:
create table #DatabaseRoles (
DBName sysname,
AccountName sysname,
AccountType varchar(60),
LoginExists char(1),
DatabaseRoles varchar(max)
)
declare @sql varchar(max)
set @sql =
'USE ?
insert into #DatabaseRoles
select [DBName] = ''?'',
[AccountName] = dp.name,
[AccountType] = dp.type_desc,
[LoginExists] = case isnull(sp.principal_id,-1)
when -1 then ''N''
else ''Y''
end,
[DatabaseRoles] = coalesce((
select dp2.name + '','' as ''data()'' from sys.database_role_members drm
inner join sys.database_principals dp2 on drm.role_principal_id = dp2.principal_id
where dp.principal_id = drm.member_principal_id
for XML PATH('''')
), '''')
from sys.database_principals dp
left outer join sys.server_principals sp on dp.sid = sp.sid
where dp.type not in (''R'',''C'')
and dp.name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')'
exec sp_MSforeachdb @sql
select * from #DatabaseRoles
drop table #DatabaseRoles
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply