on April 21, 2009
One reason I started this blog was just the idea of going through my catalog of scripts and reviewing them and sharing out what might be useful to people.
Here is a quick one I put together a while back. I was starting to work with a group of servers [at an unnamed company, always an unnamed company!]. Some of the instances had been configured long ago, and I found some linked servers where passwords had been hardcoded into the login mappings.
This can be a big security vulnerability, particularly if the option has been chosen to map all users to that login, and the login has significant powers on the other end of the linked server.
I put together this script to do a quick check on the permissions associated with all linked servers on an instance, and was able to use it to review this setup on all of the machines in my environment.
Some notes on reading the output:
- ‘Impersonate’ maps to the option ‘be made using the login’s current security context’.
- ‘All unmapped users’ is anyone not explicitly mapped to a remote login at the top of the linked server dialog.
select svr.name
, svr.data_source
, svr.product
, svr.is_linked
, svr.is_remote_login_enabled
, svr.is_rpc_out_enabled
, svr.is_data_access_enabled
, ll.local_principal_id
, [Name] = case ll.local_principal_id when 0 then 'All unmapped users'
else ISNULL(sp.name, '')
end
, [RemoteUser]= ISNULL(ll.remote_name, N'')
, [Impersonate]= CAST(ll.uses_self_credential AS bit)
from sys.servers svr
join sys.linked_logins ll on
svr.server_id=ll.server_id
LEFT OUTER JOIN sys.server_principals sp ON
ll.local_principal_id = sp.principal_id;
GO