June 8, 2012

sp_revokedbaccess (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_revokedbaccess(nvarchar @name_in_db)

MetaData:

 create procedure sys.sp_revokedbaccess  
@name_in_db sysname
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int,
@stmtU nvarchar(4000)

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

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

BEGIN TRANSACTION
-- drop user
-- for guest disable its access to database, this way guest schema is not dropped
declare @guestname sysname
set @guestname='guest'

if lower(@name_in_db) = @guestname
begin
if db_id() in (1,2)
begin
ROLLBACK TRANSACTION
raiserror(15182,-1,-1)
return (1)
end

-- don't let disable guest twice
if not exists (select * from sys.database_permissions p where p.class = 0 and p.major_id = 0 and p.minor_id = 0
and p.grantee_principal_id = 2 and p.type = 'CO' and p.state in ('G','W')
and not exists (select * from sys.database_permissions d where d.class = 0 and d.major_id = 0 and d.minor_id = 0
and d.grantee_principal_id = p.grantee_principal_id and d.type = 'CO' and d.state = 'D'))
begin
ROLLBACK TRANSACTION
raiserror(15539,-1,-1,@name_in_db)
return (1)
end
end
else
begin
-- this will drop the schema owned by the user that has the same name as the user,
-- if such schema exists
EXEC %%Owner(Name = @name_in_db).DropSchema(OwnerType = 1)
if @@error <> 0
begin
ROLLBACK TRANSACTION
-- error message comes from inside the invoke
return (1)
end
end

set @stmtU = 'drop user ' + quotename(@name_in_db, ']')

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

COMMIT TRANSACTION

-- RETURN SUCCESS --
return (0) -- sp_revokedbaccess

No comments:

Post a Comment

Total Pageviews