April 30, 2012

sp_helpsrvrolemember (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpsrvrolemember(nvarchar @srvrolename)

MetaData:

 create procedure sys.sp_helpsrvrolemember  
@srvrolename sysname = NULL
AS
if @srvrolename is not null
begin
-- VALIDATE GIVEN NAME
if not exists (select * from sys.server_principals
where name = @srvrolename and principal_id >= suser_id('sysadmin') and principal_id <= suser_id('bulkadmin'))
begin
raiserror(15412, -1, -1, @srvrolename)
return (1)
end

-- RESULT SET FOR SINGLE SERVER-ROLE
select 'ServerRole' = SUSER_NAME(rm.role_principal_id), 'MemberName' = lgn.name , 'MemberSID' = lgn.sid
from sys.server_role_members rm, sys.server_principals lgn
where rm.role_principal_id = SUSER_ID(@srvrolename) AND
rm.member_principal_id = lgn.principal_id
end
else
begin
-- RESULT SET FOR ALL FIXED SERVER-ROLES
select 'ServerRole' = SUSER_NAME(rm.role_principal_id), 'MemberName' = lgn.name, 'MemberSID' = lgn.sid
from sys.server_role_members rm, sys.server_principals lgn
where rm.role_principal_id >=3 AND rm.role_principal_id <=10 AND
rm.member_principal_id = lgn.principal_id
end

return (0) -- sp_helpsrvrolemember

No comments:

Post a Comment

Total Pageviews