April 27, 2012

sp_helpdbfixedrole (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_helpdbfixedrole(nvarchar @rolename)

MetaData:

 create procedure sys.sp_helpdbfixedrole  
@rolename sysname = NULL
AS
if @rolename is not null
begin
-- VALIDATE GIVEN NAME
if not exists (select * from sys.database_principals where name = @rolename
and principal_id >= 16384 and principal_id <= 16393)
begin
raiserror(15412, -1, -1, @rolename)
return (1)
end

-- RESULT SET FOR SINGLE FIXED-ROLE
select 'DbFixedRole' = u.name, 'Description' = p.name collate catalog_default
from sys.database_principals u, sys.role_permissions p
where u.name = @rolename and
u.principal_id >= 16384 and
u.principal_id <= 16393 and
p.number = u.principal_id and p.type = 'DBR' and p.low = 0
end
else
begin
-- RESULT SET FOR ALL FIXED-ROLES
select 'DbFixedRole' = u.name, 'Description' = p.name collate catalog_default
from sys.database_principals u, sys.role_permissions p
where u.principal_id >= 16384 and
u.principal_id <= 16393 and
p.number = u.principal_id and p.type = 'DBR' and p.low = 0
end

return (0) -- sp_helpdbfixedrole

No comments:

Post a Comment

Total Pageviews