April 18, 2012

sp_dropapprole (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_dropapprole(nvarchar @rolename)

MetaData:

 create procedure sys.sp_dropapprole  
@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_dropapprole')
return (1)
end

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

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

BEGIN TRANSACTION

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

-- drop the owner
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_dropapprole

No comments:

Post a Comment

Total Pageviews