April 30, 2012

sp_helprolemember (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_helprolemember(nvarchar @rolename)

MetaData:

 create procedure sys.sp_helprolemember  
@rolename sysname = NULL
AS
if @rolename is not null
begin
-- VALIDATE GIVEN NAME
if not exists (select * from sysusers where name = @rolename and issqlrole = 1)
begin
raiserror(15409, -1, -1, @rolename)
return (1)
end

-- RESULT SET FOR SINGLE ROLE
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.name = @rolename
and g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
order by 1, 2
end
else
begin
-- RESULT SET FOR ALL ROLES
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
order by 1, 2
end

return (0) -- sp_helprolemember

No comments:

Post a Comment

Total Pageviews