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
|
