Post reply

SQL server logs

  • March 14, 2019 at 11:24 am

    #374643

    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

    #2024825

    sqlfriends - Thursday, March 14, 2019 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

    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

    #2024841

    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

    #2024843

    sqlfriends - Monday, March 18, 2019 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

    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

    #2024846

    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

    #2024847

    sqlfriends - Monday, March 18, 2019 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.

    You need to create something to do this. There is no such thing by default.

    Sue

  • March 21, 2019 at 2:00 pm

    #2025161

    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

    #2025167

    askcoffman - Thursday, March 21, 2019 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.

    Thank you.
    Does the loginName contains the linked server name?

  • March 21, 2019 at 4:17 pm

    #2025174

    sqlfriends - Thursday, March 21, 2019 2:52 PM

    askcoffman - Thursday, March 21, 2019 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.

    Thank you.
    Does the loginName contains the linked server name?

    Yes it does.

  • March 21, 2019 at 4:56 pm

    #2025176

    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

    #2025196

    sqlfriends - Thursday, March 21, 2019 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

    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

    #2025202

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 22, 2019 at 7:46 am

    #2025204

    Lowell - Friday, March 22, 2019 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

    I've used just oledb_provider_information and included sql_text to keep the noise down.

    Sue

  • March 22, 2019 at 9:33 am

    #2025214

    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.          

  • SQL Server

    1. 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.

  • March 22, 2019 at 11:02 am

    #2025239

    sqlfriends - Friday, March 22, 2019 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.          

  • SQL Server

    1. 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