April 13, 2012

sp_addlinkedsrvlogin (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_addlinkedsrvlogin(nvarchar @rmtsrvname
, varchar @useself
, nvarchar @locallogin
, nvarchar @rmtuser
, nvarchar @rmtpassword)

MetaData:

 create procedure sys.sp_addlinkedsrvlogin  
@rmtsrvname sysname,
@useself varchar(8) = 'true',
@locallogin sysname = NULL,
@rmtuser sysname = NULL,
@rmtpassword sysname = NULL
as
declare @pwd varbinary(256),
@localid int,
@useselfbit bit,
@ret int

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

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


-- VALIDATE @useself PARAMETER --
-- IF @useself IS TRUE IT OVERRIDES PARAMETERS @rmtuser and @rmtpassword
select @useself = lower(@useself), @useselfbit = CASE WHEN lower(@useself) = 'true' THEN 1 ELSE 0 END
if @useself is null or @useself not in ('true','false')
begin
raiserror(15600,-1,-1,'sys.sp_addlinkedsrvlogin')
return 1
end
if @useselfbit = 1
select @rmtuser = NULL, @rmtpassword = NULL

BEGIN TRAN

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

-- IF SPECIFIED CHECK LOCAL USER NAME (NO NT GROUP!)
select @localid = 0
if (@locallogin IS NOT NULL)
begin
-- share-lock the local login
EXEC %%LocalLogin ( Name = @locallogin ) . Lock ( Exclusive = 0 )
IF @@ERROR = 0
select @localid = principal_id from sys.server_principals
where name = @locallogin and type in ('S', 'U')
else
begin
-- ADD ROW FOR NT USER LOGIN IF NEEDED --
if (get_sid('\U'+@locallogin) IS NOT NULL)
begin
EXEC @ret = sys.sp_MSaddlogin_implicit_ntlogin @locallogin
if (@ret = 0)
select @localid = principal_id from sys.server_principals
where name = @locallogin and type = 'U'
end
end
if (@localid = 0)
begin
ROLLBACK TRAN
raiserror(15007,-1,-1,@locallogin)
return (1)
end
end

-- IF MAPPING ALREADY EXISTS FOR THIS SID, REMOVE IT (compatibility) --
IF EXISTS (SELECT * FROM sys.linked_logins WHERE
server_id IN (SELECT server_id FROM sys.servers where name = @rmtsrvname)
AND local_principal_id = @localid)
BEGIN
EXEC %%LinkedServer(Name=@rmtsrvname).RemoveLinkedLogin(LocalID=@localid)
END

-- ADD THE SPECIFIED MAPPING --
SELECT @pwd = convert(varbinary(256), @rmtpassword)
EXEC %%LinkedServer(Name=@rmtsrvname).NewLinkedLogin(
LocalID=@localid, UseSelf=@useselfbit, RemoteName=@rmtuser, Password=@pwd)

-- SHOULDN'T GET A DUPLICATE (JUST DROPPED IF THERE), BUT DO THIS AS AN ASSERT --
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
raiserror(15185,-1,-1,@rmtuser,@locallogin,@rmtsrvname)
return (1)
END

-- EMDEventType(x_eet_Create_Linked_Server_Login), EMDUniversalClass(x_eunc_Linked_Server), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
-- Note: we do not pass @rmtpassword since it contains a password
EXEC %%System().FireTrigger(ID = 226, ID = 102, ID = 0, ID = 0, Value = @rmtsrvname,
ID = 101, ID = @localid, ID = 0, Value = NULL,
ID = 5, Value = @rmtsrvname, Value = @useself, Value = @locallogin, Value = @rmtuser, Value = NULL, Value = NULL, Value = NULL)

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

No comments:

Post a Comment

Total Pageviews