April 13, 2012

sp_addremotelogin (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_addremotelogin(nvarchar @remoteserver
, nvarchar @loginame
, nvarchar @remotename)

MetaData:

 create procedure sys.sp_addremotelogin  
@remoteserver sysname, -- name of remote server
@loginame sysname = NULL, -- user's local user name
@remotename sysname = NULL -- user's remote name
as
declare @localid int

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

-- CHECK PERMISSIONS
if not (has_perms_by_name(null, null, 'alter any login') = 1)
begin
EXEC %%System().AuditEvent(ID = 1380142162, Success = 0, TargetLoginName = @loginame, TargetUserName = NULL, Role = NULL, Object = @remotename, Provider = NULL, Server = @remoteserver)
raiserror(15247,-1,-1)
return (1)
end
else
EXEC %%System().AuditEvent(ID = 1380142162, Success = 1, TargetLoginName = @loginame, TargetUserName = NULL, Role = NULL, Object = @remotename, Provider = NULL, Server = @remoteserver)

-- CHECK FOR INVALID PARAMETER SYNTAX --
if @remoteserver is null OR (@loginame is null and @remotename is not null)
begin
raiserror(15600,-1,-1,'sys.sp_addremotelogin')
return (1)
end

BEGIN TRAN

-- VALIDATE/LOCK SERVER NAME --
EXEC %%LinkedServer ( Name = @remoteserver ) . Lock ( Exclusive = 1 )
IF @@ERROR <> 0
begin
ROLLBACK TRAN
raiserror(15015,-1,-1,@remoteserver)
return (1)
end

-- VALIDATE/LOCK @loginame --
select @localid = 0
if @loginame is not null
begin
-- share-lock the local login
EXEC %%LocalLogin ( Name = @loginame ) . Lock ( Exclusive = 0 )
IF @@ERROR = 0
select @localid = principal_id from sys.server_principals
where name = @loginame and type = 'S' -- cannot map to NT login
if @localid = 0
begin
ROLLBACK TRAN
raiserror(15007,-1,-1,@loginame)
return (1)
end
end

-- ADD NEW REMOTE-LOGIN (WILL CHECK FOR DUPLICATE <@remoteserver, @remotename> PAIR)
EXEC %%LinkedServer(Name=@remoteserver).NewRemoteLogin( RemoteName = @remotename, LocalID = @localid )
IF @@ERROR <> 0
begin
ROLLBACK TRAN
if @remotename is null
raiserror(15066,-1,-1,@remoteserver)
else
raiserror(15068,-1,-1,@remotename,@remoteserver)
return (1)
end

-- SUCCESS --
COMMIT TRAN
return (0) -- sp_addremotelogin

No comments:

Post a Comment

Total Pageviews