你查看一下这个存储过程的代码:
create procedure sp_addlinkedsrvlogin
@rmtsrvname sysname,
@useself varchar(8) = 'true',
@locallogin sysname = NULL,
@rmtuser sysname = NULL,
@rmtpassword sysname = NULL
as
declare @srvid smallint,
@status smallint,
@localsid varbinary(85),
@ret int -- DISALLOW USER TRANSACTION
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sp_addlinkedsrvlogin')
return (1)
end -- CHECK PERMISSIONS
if not (is_srvrolemember('securityadmin') = 1)
begin
raiserror(15247,-1,-1)
return (1)
end -- VALIDATE @useself PARAMETER --
select @useself = lower(@useself)
if @useself is null or @useself not in ('true','false')
begin
raiserror(15600,-1,-1,'sp_addlinkedsrvlogin')
return 1
end -- CHECK REMOTE SERVER NAME.
select @srvid = srvid from master.dbo.sysservers where srvname = @rmtsrvname
if @srvid is null
begin
raiserror(15015,-1,-1,@rmtsrvname)
return (1)
end -- IF SPECIFIED CHECK LOCAL USER NAME
if (@locallogin IS NOT NULL)
begin
select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
if @localsid IS NULL
begin
-- ADD ROW FOR NT LOGIN IF NEEDED --
execute @ret = sp_MSaddlogin_implicit_ntlogin @locallogin
if (@ret = 0)
select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
if (@localsid IS NULL)
begin
raiserror(15067,-1,-1,@locallogin)
return (1)
end
end
end -- 64 IMPLIES sysxlogins::ishqoutmap is TRUE
select @status = 64 -- IF @useself IS TRUE IT OVERRIDES PARAMETERS @rmtuser, and @rmtpassword
if @useself = 'true'
begin
select @rmtuser = NULL
select @rmtpassword = NULL
select @status = @status | 128
end BEGIN TRAN -- DELETE EXISTING MAPPING(s) FOR THIS @sid
update master.dbo.sysxlogins set xstatus = xstatus & ~192
where srvid = @srvid AND ishqoutmap = 1 AND isrpcinmap = 1
AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
if @@rowcount = 0
delete master.dbo.sysxlogins where srvid = @srvid AND ishqoutmap = 1
AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid) -- ATTEMPT TO TAG THIS ONTO EXISTING ROW --
update master.dbo.sysxlogins
set xstatus = (xstatus & ~192) | @status,
xdate2 = getdate(),
password = convert(varbinary(256), encrypt(@rmtpassword))
where srvid = @srvid AND isrpcinmap = 1
AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
AND ((name IS NULL AND @rmtuser IS NULL) OR name = @rmtuser) -- IF NO ROW UPDATED, INSERT NEW ROW --
if (@@rowcount = 0)
insert master.dbo.sysxlogins values
(@srvid, @localsid, @status, getdate(), getdate(), @rmtuser,
convert(varbinary(256), encrypt(@rmtpassword)), 0, NULL) COMMIT TRAN -- RETURN SUCCESS
return(0) -- sp_addlinkedsrvlogin
create procedure sp_addlinkedsrvlogin
@rmtsrvname sysname,
@useself varchar(8) = 'true',
@locallogin sysname = NULL,
@rmtuser sysname = NULL,
@rmtpassword sysname = NULL
as
declare @srvid smallint,
@status smallint,
@localsid varbinary(85),
@ret int -- DISALLOW USER TRANSACTION
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sp_addlinkedsrvlogin')
return (1)
end -- CHECK PERMISSIONS
if not (is_srvrolemember('securityadmin') = 1)
begin
raiserror(15247,-1,-1)
return (1)
end -- VALIDATE @useself PARAMETER --
select @useself = lower(@useself)
if @useself is null or @useself not in ('true','false')
begin
raiserror(15600,-1,-1,'sp_addlinkedsrvlogin')
return 1
end -- CHECK REMOTE SERVER NAME.
select @srvid = srvid from master.dbo.sysservers where srvname = @rmtsrvname
if @srvid is null
begin
raiserror(15015,-1,-1,@rmtsrvname)
return (1)
end -- IF SPECIFIED CHECK LOCAL USER NAME
if (@locallogin IS NOT NULL)
begin
select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
if @localsid IS NULL
begin
-- ADD ROW FOR NT LOGIN IF NEEDED --
execute @ret = sp_MSaddlogin_implicit_ntlogin @locallogin
if (@ret = 0)
select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
if (@localsid IS NULL)
begin
raiserror(15067,-1,-1,@locallogin)
return (1)
end
end
end -- 64 IMPLIES sysxlogins::ishqoutmap is TRUE
select @status = 64 -- IF @useself IS TRUE IT OVERRIDES PARAMETERS @rmtuser, and @rmtpassword
if @useself = 'true'
begin
select @rmtuser = NULL
select @rmtpassword = NULL
select @status = @status | 128
end BEGIN TRAN -- DELETE EXISTING MAPPING(s) FOR THIS @sid
update master.dbo.sysxlogins set xstatus = xstatus & ~192
where srvid = @srvid AND ishqoutmap = 1 AND isrpcinmap = 1
AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
if @@rowcount = 0
delete master.dbo.sysxlogins where srvid = @srvid AND ishqoutmap = 1
AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid) -- ATTEMPT TO TAG THIS ONTO EXISTING ROW --
update master.dbo.sysxlogins
set xstatus = (xstatus & ~192) | @status,
xdate2 = getdate(),
password = convert(varbinary(256), encrypt(@rmtpassword))
where srvid = @srvid AND isrpcinmap = 1
AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
AND ((name IS NULL AND @rmtuser IS NULL) OR name = @rmtuser) -- IF NO ROW UPDATED, INSERT NEW ROW --
if (@@rowcount = 0)
insert master.dbo.sysxlogins values
(@srvid, @localsid, @status, getdate(), getdate(), @rmtuser,
convert(varbinary(256), encrypt(@rmtpassword)), 0, NULL) COMMIT TRAN -- RETURN SUCCESS
return(0) -- sp_addlinkedsrvlogin
解决方案 »
- ALTER DATABASE 语句中的database不支持变量吗?
- 几条查询语句,请高手进来看一下
- 有关数据合并的存储过程写法问题.
- 声明变量出错
- 小弟是一个菜鸟,问两个最基础的问题
- 数据查询
- 如何使用group 实现数据区间统计,如:num>10,0<num<10,num<0这样实现数据统计,急急,谢谢!
- 求一查询sql语句,在线等!分不多,大家帮帮我
- 分类求和问题!!
- SQL 7.0's replication
- 对自己的表和 northwind的customers表使用索引优化向导后,显示没有优化建议,难道不起作用??
- 在sql server中代替的oracle的 decode函数是什么?
go
exec sp_helptext sp_addlinkedsrvlogin