April 13, 2012

sp_addsrvrolemember (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_addsrvrolemember(nvarchar @loginame
, nvarchar @rolename)

MetaData:

 --  FOR BACKWARD COMPATIBILTY ONLY --   
create procedure sys.sp_addsrvrolemember
@loginame sysname, -- login name
@rolename sysname = NULL -- server role name
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int, -- return value of sp call
@stmtR nvarchar(4000)

-- Was a part of check to prevent inside a user Xact. Leave for backward compat.--
set implicit_transactions off

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

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

BEGIN TRANSACTION
-- LOCK LOGIN --
EXEC %%LocalLogin(Name = @loginame).Lock(Exclusive = 1)

-- ADD ROW FOR NT LOGIN IF NEEDED --
if @@error <> 0 -- not found
begin
execute @ret = sys.sp_MSaddlogin_implicit_ntlogin @loginame
if (@ret <> 0)
begin
ROLLBACK TRANSACTION
raiserror(15007,-1,-1,@loginame)
return (1)
end
-- login locked
end

set @stmtR = 'alter server role '
set @stmtR = @stmtR + quotename(@rolename, ']')
set @stmtR = @stmtR + ' add member '
set @stmtR = @stmtR + quotename(@loginame, ']')

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

-- RETURN SUCCESS --
COMMIT TRANSACTION
return (0) -- sp_addsrvrolemember

No comments:

Post a Comment

Total Pageviews