May 8, 2008 at 3:31 am
I tried to create a script that will function similar to sp_helpsrvrolemember but I want to return all logins, not just the one having server roles,
here is the script:
[font="Courier New"]SELECT DISTINCT(sxl.name) AS MemberName,
CASE
WHEN
spv.number & sxl.xstatus = spv.number THEN spv.name
ELSE ' -- '
END AS ServerRole
FROM master..sysxlogins sxl, master..spt_values spv
WHERE spv.low = -1
AND spv.type = 'SRV'
AND sxl.srvid IS NULL[/font]
any reason why there are duplicate entries when a login has serverrole. When I add the login to all fixed server role, the one without data in serverrole column is gone? I am not good in TSQL so any idea how can I get my desired result(the one without duplicate)? Thanks in advance for any help. I appreciate it.
:-):cool:
May 8, 2008 at 3:41 am
You will need to use a LEFT JOIN so that you get all the logins, and not just the ones with server roles. The query you have written will return every distinct combination of login and server role.
John
May 8, 2008 at 3:51 am
I have tried that. but didn't give me the desired result.
:-):cool:
May 8, 2008 at 3:56 am
You're going to have to try harder than that if you want any of us to be able to help you. Please show us what you tried, and state in what way it didn't produce the desired results. Some sample data and expected results would come in handy.
John
May 8, 2008 at 4:11 am
Here you go...
[font="Courier New"]QUERY:
SELECT DISTINCT(sxl.name) AS MemberName,
CASE
WHEN
spv.number & sxl.xstatus = spv.number THEN spv.name
ELSE ' -- '
END AS ServerRole
FROM master..spt_values spv JOIN master..sysxlogins sxl
ON spv.low = -1
AND spv.type = 'SRV'
AND sxl.srvid IS NULL
RESULT:
MemberNameServerRole
----------------------------------------------------------------
BUILTIN\Administrators--
BUILTIN\AdministratorsSystem Administrators
sa--
saSystem Administrators
QUERY LEFT JOIN:
SELECT DISTINCT(sxl.name) AS MemberName,
CASE
WHEN
spv.number & sxl.xstatus = spv.number THEN spv.name
ELSE ' -- '
END AS ServerRole
FROM master..spt_values spv LEFT JOIN master..sysxlogins sxl
ON spv.low = -1
AND spv.type = 'SRV'
AND sxl.srvid IS NULL
RESULT LEFT JOIN
MemberNameServerRole
----------------------------------------------------------------
NULL--
BUILTIN\Administrators--
BUILTIN\AdministratorsSystem Administrators
sa--
saSystem Administrators
QUERY WITHOUT DISTICT JOIN
SELECT sxl.name AS MemberName,
CASE
WHEN
spv.number & sxl.xstatus = spv.number THEN spv.name
ELSE ' -- '
END AS ServerRole
FROM master..sysxlogins sxl JOIN master..spt_values spv
ON spv.low = -1
AND spv.type = 'SRV'
AND sxl.srvid IS NULL
RESULT:
MemberNameServerRole
----------------------------------------------------------------
BUILTIN\AdministratorsSystem Administrators
saSystem Administrators
BUILTIN\Administrators--
sa--
BUILTIN\Administrators--
sa--
(repeated 5x more)
--
(16 row(s) affected)
[/font]
:-):cool:
May 8, 2008 at 6:03 am
by the way...
my SQL instance here has only SA and Builtin\Admin in the logins... but if there are other logins without server roles, it will just be included in the result set but not duplicate, only logins with server roles have duplicate entries in the result set. I want to remove those duplicate in the result set.
Thanks again...
:-):cool:
May 8, 2008 at 6:50 am
I think a better way would be to store the results of sp_helpsrvrolemember into a temp table, then LEFT JOIN syslogins to that so that you also get logins that aren't a member of any server role.
John
May 8, 2008 at 7:00 pm
ok... I will try that... thanks.
:-):cool:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply