T-SQL:
Create Permanent Logging Table:
Create Table dbo.LOG_LINKED_SERVERS
(LogID INT Primary Key Identity(1,1),
ServerID int,
LinkedServerName varchar(150),
ServerName varchar(150),
Product varchar(150),
[Provider] varchar(50),
[Catalog] varchar(150),
[Local Login] varchar(100),
[Remote Login Name] varchar(100),
[RPC Out Enabled] varchar(10),
[Data Access Enabled] varchar(10),
Modify_Date datetime,
InsertedDate datetime)
Insert Data:
Insert into dbo.LOG_LINKED_SERVERS
(ServerID, LinkedServerName, ServerName, Product, [Provider], [Catalog], [Local Login],
[Remote Login Name], [RPC Out Enabled], [Data Access Enabled], Modify_Date, InsertedDate)
SELECT ss.server_id
,ss.name
,'Server ' = Case ss.Server_id when 0 then 'Current Server' else 'Remote Server'end
,ss.product
,ss.provider
,ss.catalog
,'Local Login ' = case sl.uses_self_credential when 1 then 'Uses Self Credentials' else ssp.name end
,'Remote Login Name' = sl.remote_name
,'RPC Out Enabled' = case ss.is_rpc_out_enabled when 1 then 'True' else 'False' end
,'Data Access Enabled' = case ss.is_data_access_enabled when 1 then 'True' else 'False' end
,ss.modify_date,
GetDate()
FROM [MASTER].sys.Servers ss
LEFT JOIN [MASTER].sys.linked_logins sl ON ss.server_id = sl.server_id
LEFT JOIN [MASTER].sys.server_principals ssp ON ssp.principal_id = sl.local_principal_id
Order by ss.name asc
|
|
|
|
|
|