March 14, 2019 at 11:24 am
I have a question about SQL server logs and SQL server error logs. What usually are logged, I can see server server start up info, shutdown, backup information, failed logins, checkdb , sp_config info, what other info is logged?
I have a particular question about logs for SQL server linked servers. Is there a place that I can see logs regarding who used the linked server to query and what data accessed through linked servers?
Thanks
March 18, 2019 at 1:38 pm
sqlfriends - Thursday, March 14, 2019 11:24 AMI have a question about SQL server logs and SQL server error logs. What usually are logged, I can see server server start up info, shutdown, backup information, failed logins, checkdb , sp_config info, what other info is logged?
I have a particular question about logs for SQL server linked servers. Is there a place that I can see logs regarding who used the linked server to query and what data accessed through linked servers?Thanks
No that's not logged. You'd need to setup your own auditing of some sort to capture that information. You could do something like extended events looking for the sql statement which filters on the name of the linked server.
Sue
March 18, 2019 at 2:59 pm
How about the database permission log? Does any database or table level permission logged in anywhere if user don't have permission to the objects? ( they do have login). Any log by default? THanks
March 18, 2019 at 3:34 pm
sqlfriends - Monday, March 18, 2019 2:59 PMHow about the database permission log? Does any database or table level permission logged in anywhere if user don't have permission to the objects? ( they do have login). Any log by default? THanks
I'm not sure what you mean by the database permissions log - I don't have one. If you are referring to the SQL Server error log, it not logged there by default.
I'm not aware of a default location where permissions denied to objects is logged.
Sue
March 18, 2019 at 3:46 pm
Thanks, I meant a place that logs database level or objects access in database denied messages.
It looks like your answer is there is not a place like that you are aware of.
March 18, 2019 at 3:56 pm
sqlfriends - Monday, March 18, 2019 3:46 PMThanks, I meant a place that logs database level or objects access in database denied messages.It looks like your answer is there is not a place like that you are aware of.
You need to create something to do this. There is no such thing by default.
Sue
March 21, 2019 at 2:00 pm
You can use sql Profiler to watch for linked server traffic. Just filter the loginName column by whatever linked servers you are looking for. In the Events Selection, be sure to include Audit Login, SP:StmtStarting and any other column you want so you can see when and what they're doing.
March 21, 2019 at 2:52 pm
askcoffman - Thursday, March 21, 2019 2:00 PMYou can use sql Profiler to watch for linked server traffic. Just filter the loginName column by whatever linked servers you are looking for. In the Events Selection, be sure to include Audit Login, SP:StmtStarting and any other column you want so you can see when and what they're doing.
Thank you.
Does the loginName contains the linked server name?
March 21, 2019 at 4:17 pm
sqlfriends - Thursday, March 21, 2019 2:52 PMaskcoffman - Thursday, March 21, 2019 2:00 PMYou can use sql Profiler to watch for linked server traffic. Just filter the loginName column by whatever linked servers you are looking for. In the Events Selection, be sure to include Audit Login, SP:StmtStarting and any other column you want so you can see when and what they're doing.Thank you.
Does the loginName contains the linked server name?
Yes it does.
March 21, 2019 at 4:56 pm
I thought the LoginName is the windows user login.
Could you give an example what the login looks like that includes linked servername?
Thanks
March 22, 2019 at 6:57 am
sqlfriends - Thursday, March 21, 2019 4:56 PMI thought the LoginName is the windows user login.Could you give an example what the login looks like that includes linked servername?
Thanks
You're correct, it will only show the login of the sql login/windows user running the linked server. After a short google sprint it looks like profiler cannot do exactly what you need. But if you have an idea of which sql servers are using the linked server you could filter on the HostName.
March 22, 2019 at 7:30 am
you could create an extended event to capture linked server activity, based on the event sqlserver.oledb_data_read, but what are you trying to capture?
my testing of this EE was a little chatty for me.
CREATE EVENT SESSION [EE_Linked_Server_Tracking] ON SERVER
ADD EVENT sqlserver.oledb_data_read
ADD TARGET package0.event_file(SET FILENAME=N'EE_Linked_Server_Tracking')
WITH (MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=OFF
)
GO
Lowell
March 22, 2019 at 7:46 am
Lowell - Friday, March 22, 2019 7:30 AMyou could create an extended event to capture linked server activity, based on the event sqlserver.oledb_data_read, but what are you trying to capture?
my testing of this EE was a little chatty for me.
CREATE EVENT SESSION [EE_Linked_Server_Tracking] ON SERVER
ADD EVENT sqlserver.oledb_data_read
ADD TARGET package0.event_file(SET FILENAME=N'EE_Linked_Server_Tracking')
WITH (MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=OFF
)
GO
I've used just oledb_provider_information and included sql_text to keep the noise down.
Sue
March 22, 2019 at 9:33 am
Can I ask why use OLEDB provider? I would like to catch who used the linked server and failed, and who accessed linked server data and what data is accessed. I get it now I can use sqltext to filter out linked server name.
Does linked server query all use OLEDB provider ?
I create the linked server use the first option SQL server. I am not using other data source.
March 22, 2019 at 11:02 am
sqlfriends - Friday, March 22, 2019 9:33 AMCan I ask why use OLEDB provider? I would like to catch who used the linked server and failed, and who accessed linked server data and what data is accessed. I get it now I can use sqltext to filter out linked server name.Does linked server query all use OLEDB provider ?
I create the linked server use the first option SQL server. I am not using other data source.
- SQL Server
- Identify the linked server as an instance of MicrosoftSQL Server. If you use this method of defining a SQL Server linked server, the name specified in Linked server must be the network name of the server. Also, any tables retrieved from the server are from the default database defined for the login on the linked server.
Linked servers use OLE DB providers as indicated in the documentation for linked servers:
Linked Servers (Database Engine)
When selecting SQL Server and just the network name without specifying a provider, it uses the SQL Server Native Client OLE DB provider as listed in the remarks section of this documentation as well as in the explanation for the @provider argument:
sp_addlinkedserver (Transact-SQL)
Sue
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply