April 13, 2012

sp_addrole (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_addrole(nvarchar @rolename
, nvarchar @ownername)

MetaData:

 create procedure sys.sp_addrole  
@rolename sysname, -- name of new role
@ownername sysname = NULL -- name of owner of new role
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int, -- return value of sp call
@owner int,
@stmtR nvarchar(4000),
@stmtS nvarchar(4000)

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
if (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sys.sp_addrole')
return (1)
end

exec @ret = sys.sp_validname @rolename
if @ret <> 0
return(1)

if @ownername is null
select @ownername = user_name()

set @stmtR = 'create role '
set @stmtR = @stmtR + quotename(@rolename, ']')
set @stmtR = @stmtR + ' authorization '
set @stmtR = @stmtR + quotename(@ownername, ']')

-- Create Schema if not one exists with same name and owner
set @stmtS = 'create schema '
set @stmtS = @stmtS + quotename(@rolename, ']')
set @stmtS = @stmtS + ' authorization '
set @stmtS = @stmtS + quotename(@rolename, ']')

BEGIN TRANSACTION

-- issue DDL to create the role --
exec (@stmtR)
if @@error <> 0
begin
ROLLBACK TRANSACTION
return (1)
end


-- create the Schema if not one exists with same name and owner
declare @schema_uid int
declare @owner_uid int
select @owner_uid = principal_id from sys.database_principals where name = @rolename
select @schema_uid = principal_id from sys.schemas where name = @rolename
if (@schema_uid is null or -- there is no schema since if there is one, it will have an owner
@schema_uid <> @owner_uid) -- for error message
begin
exec (@stmtS)
if @@error <> 0
begin
ROLLBACK TRANSACTION
return (1)
end
end

COMMIT TRANSACTION

-- RETURN SUCCESS --
return (0) -- sp_addrole

No comments:

Post a Comment

Total Pageviews