April 22, 2012

sp_droprole (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_droprole(nvarchar @rolename)

MetaData:

 create procedure sys.sp_droprole  
@rolename sysname -- role to be dropped
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int,
@stmtR nvarchar(4000)

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

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

-- create statement
set @stmtR = 'drop role ' + quotename(@rolename)

BEGIN TRANSACTION

-- this will drop the schema owned by the role that has the same name as the role,
-- if such schema exists
EXEC %%Owner(Name = @rolename).DropSchema(OwnerType = 2)
if @@error <> 0
begin
ROLLBACK TRANSACTION
-- error message comes from inside the invoke
return (1)
end

-- drop the role
exec (@stmtR)
if @@error <> 0
begin
ROLLBACK TRANSACTION
-- error message comes from inside the statement
return (1)
end

COMMIT TRANSACTION

-- RETURN SUCCESS --
return (0) -- sp_droprole

No comments:

Post a Comment

Total Pageviews