April 26, 2007 at 12:55 am
Hi,
In SQL Server 2000 I used a simple query to list all server roles members:
select a.name RoleName, b.name LoginName from master.dbo.spt_values a (nolock), master.dbo.sysxlogins b (nolock)
where a.low = 0 and a.type = 'srv' and b.srvid is null and a.number & b.xstatus = a.number order by a.name desc, b.name
To get the same output in SQL Server 2005 I could only come up with this:
select a.name, 'sysadmin' from master..syslogins (nolock) a
join master..syslogins (nolock) b
on a.name=b.name where a.sysadmin =1
union
select a.name, 'securityadmin' from master..syslogins (nolock) a
join master..syslogins (nolock) b
on a.name=b.name where a.securityadmin =1
union
select a.name, 'serveradmin' from master..syslogins (nolock) a
join master..syslogins (nolock) b
on a.name=b.name where a.serveradmin =1
...
-- and so forth for each role
Does anyone know about a more elegant way to produce the same report in SQL Server 2005?
Thanks.
April 26, 2007 at 5:31 am
This should do it:
SELECT
p.name,m.name
FROM
sys.server_principals p
JOIN
sys.server_role_members r
ON p.TYPE = 'R'
AND p.Principal_id = r.role_principal_id
JOIN
sys.server_principals m
ON r.member_principal_id = m.principal_id
ORDER
BY p.NAME,m.name
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply