April 27, 2012

sp_helpremotelogin (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_helpremotelogin(nvarchar @remoteserver
, nvarchar @remotename)

MetaData:

 create procedure sys.sp_helpremotelogin  
@remoteserver sysname = NULL, -- remote server name --
@remotename sysname = NULL -- remote login name --
as
set nocount on

-- If no server given, get 'em all.
if not exists (select * from master.dbo.sysservers s, master.dbo.sysremotelogins r
where s.srvid = r.remoteserverid
and (@remoteserver is null or s.srvname = @remoteserver ))
begin
if @remoteserver is null
begin
raiserror(15200,-1,-1)
return (0)
end

raiserror(15201,-1,-1,@remoteserver)
return (1)
end

-- If no remotename given, get 'em all.
if not exists (select * from master.dbo.sysremotelogins
where (@remotename is null or isnull(remoteusername, ' ') = @remotename))
begin
if @remotename is null
begin
raiserror(15202,-1,-1)
return (1)
end

raiserror(15203,-1,-1,@remotename)
return (1)

end

-- Check for empty results.
if not exists (select *
from master.dbo.sysremotelogins r, master.dbo.sysservers s
where ( @remotename is null or isnull(r.remoteusername, ' ') = @remotename)
and s.srvid = r.remoteserverid
and (@remoteserver is null or s.srvname = @remoteserver))
begin
raiserror(15204,-1,-1,@remotename,@remoteserver)
return (1)
end

-- select the information.
select server = substring(s.srvname, 1, 22),
local_user_name =
substring(isnull(suser_sname(r.sid), '-- use local name -- '), 1, 22),
remote_user_name =
substring(isnull(r.remoteusername, '-- mapped locally -- '), 1, 22),
options = case
when datalength(v.name) is null then ''
when datalength(v.name) = 0 then ''
else substring(v.name, 1, 9)
end
from master.dbo.sysservers s, master.dbo.sysremotelogins r,
master.dbo.spt_values v
where s.srvid = r.remoteserverid
and (@remoteserver is null or s.srvname = @remoteserver)
and (@remotename is null or isnull(r.remoteusername, ' ') = @remotename)
and v.type = 'F'
and v.number = r.status
order by server, remote_user_name

return (0) -- sp_helpremotelogin

No comments:

Post a Comment

Total Pageviews