Login Lookup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Author

Topic

Robert London
Starting Member

26 Posts

Posted - 2011-04-14 : 05:16:27

Hi,

Please could you let me know if there is an easy way to check who has logged on to SQL 2008?

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-14 : 05:20:59

Hey try sp_who
and
sp_who2

Raghu' S

Robert London
Starting Member

26 Posts

Posted - 2011-04-14 : 05:43:33

Hi Thanks.

SSMS was running a query last night and stopped and shut down due to Error: 17054. (DB is now in recovery) When I went to log on this morning it would not let me connect so I went into Services and changed logon to Local System Account and I can now get on.

What I want to know is what caused this to happen. Can anyone advise please?

Here is the log:

Date,Source,Severity,Message
04/14/2011 03:22:24,spid15s,Unknown,The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.
04/14/2011 03:22:24,spid15s,Unknown,Error: 17054<c/> Severity: 16<c/> State: 1.
04/14/2011 03:22:24,spid15s,Unknown,Service Broker manager has shut down.
04/14/2011 03:22:19,Server,Unknown,SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
04/14/2011 00:00:51,spid12s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/13/2011 23:42:04,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/13/2011 23:42:04,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/13/2011 11:42:02,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/13/2011 11:42:02,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/13/2011 00:00:58,spid21s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/12/2011 23:42:01,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/12/2011 23:42:01,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/12/2011 11:42:00,spid54,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/12/2011 11:42:00,spid54,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/12/2011 00:00:26,spid12s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/11/2011 23:41:59,spid54,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/11/2011 23:41:59,spid54,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/11/2011 11:41:58,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/11/2011 11:41:58,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/11/2011 00:00:14,spid20s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/10/2011 23:41:58,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/10/2011 23:41:58,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/10/2011 11:44:20,spid62,Unknown,Process ID 60 was killed by hostname BSQL01<c/> host process ID 2500.
04/10/2011 11:44:19,spid62,Unknown,Process ID 60 was killed by hostname BSQL01<c/> host process ID 2500.
04/10/2011 11:41:53,spid56,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/10/2011 11:41:53,spid56,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/10/2011 00:00:04,spid12s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/09/2011 23:41:52,spid56,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/09/2011 23:41:52,spid56,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/09/2011 11:41:51,spid57,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/09/2011 11:41:51,spid57,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/09/2011 00:00:49,spid24s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/08/2011 23:41:50,spid57,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/08/2011 23:41:50,spid57,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/08/2011 11:41:48,spid54,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/08/2011 11:41:48,spid54,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/08/2011 00:00:39,spid19s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/07/2011 23:41:47,spid54,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/07/2011 23:41:47,spid54,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/07/2011 11:41:46,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/07/2011 11:41:46,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/07/2011 00:00:24,spid25s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/06/2011 23:41:45,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/06/2011 23:41:45,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/06/2011 11:41:44,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/06/2011 11:41:44,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/06/2011 00:00:28,spid24s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/05/2011 23:41:43,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/05/2011 23:41:43,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/05/2011 11:41:43,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/05/2011 11:41:42,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/05/2011 00:00:48,spid25s,Unknown,This instance of SQL Server has been using a process ID of 1372 since 04/04/2011 11:41:44 (local) 04/04/2011 10:41:44 (UTC). This is an informational message only; no user action is required.
04/04/2011 23:41:42,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/04/2011 23:41:42,spid52,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/04/2011 15:33:43,spid52,Unknown,Starting up database 'DM_TEST_V1'.
04/04/2011 14:57:34,spid53,Unknown,Starting up database 'BUSINESS'.
04/04/2011 14:55:31,Logon,Unknown,Login failed for user 'administrator'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
04/04/2011 14:55:31,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 58.
04/04/2011 14:55:15,Logon,Unknown,Login failed for user 'administrator'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
04/04/2011 14:55:15,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 58.
04/04/2011 14:44:41,spid55,Unknown,Starting up database 'EMAIL'.
04/04/2011 13:27:37,spid55,Unknown,Starting up database 'DI'.
04/04/2011 13:27:15,spid55,Unknown,Starting up database 'EMAIL'.
04/04/2011 13:08:32,Logon,Unknown,Login failed for user 'BSQL01\Administrator'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
04/04/2011 13:08:32,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
04/04/2011 13:04:14,spid54,Unknown,Using 'xpstar.dll' version '2007.100.4000' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
04/04/2011 13:04:14,spid54,Unknown,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
04/04/2011 11:41:47,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.
04/04/2011 11:41:47,spid51,Unknown,Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.
04/04/2011 11:41:44,spid6s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
04/04/2011 11:41:43,spid27s,Unknown,Starting up database 'ReportServerTempDB'.
04/04/2011 11:41:43,spid26s,Unknown,Starting up database 'ReportServer'.
04/04/2011 11:41:43,spid12s,Unknown,Starting up database 'msdb'.
04/04/2011 11:41:43,spid12s,Unknown,A new instance of the full-text filter daemon host process has been successfully started.
04/04/2011 11:41:40,Logon,Unknown,Login failed for user 'BSQL01\Administrator'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
04/04/2011 11:41:40,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
04/04/2011 11:41:39,Logon,Unknown,Login failed for user 'BSQL01\Administrator'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
04/04/2011 11:41:39,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
04/04/2011 11:41:37,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
04/04/2011 11:41:37,Server,Unknown,The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b<c/> state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
04/04/2011 11:41:37,Server,Unknown,Dedicated admin connection support was established for listening locally on port 1434.
04/04/2011 11:41:37,Server,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 1434].
04/04/2011 11:41:37,Server,Unknown,Server is listening on [ ::1 <ipv6> 1434].
04/04/2011 11:41:37,spid15s,Unknown,Service Broker manager has started.
04/04/2011 11:41:37,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
04/04/2011 11:41:37,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
04/04/2011 11:41:37,Server,Unknown,Server is listening on [ 'any' <ipv4> 1433].
04/04/2011 11:41:37,Server,Unknown,Server is listening on [ 'any' <ipv6> 1433].
04/04/2011 11:41:37,Server,Unknown,A self-generated certificate was successfully loaded for encryption.
04/04/2011 11:41:37,spid15s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
04/04/2011 11:41:37,spid15s,Unknown,The Service Broker protocol transport is disabled or not configured.
04/04/2011 11:41:37,spid12s,Unknown,Starting up database 'tempdb'.
04/04/2011 11:41:37,spid12s,Unknown,Clearing tempdb database.
04/04/2011 11:41:37,spid12s,Unknown,Starting up database 'model'.
04/04/2011 11:41:37,spid6s,Unknown,Server name is 'BSQL01'. This is an informational message only. No user action is required.
04/04/2011 11:41:36,spid6s,Unknown,The resource database build version is 10.00.4000. This is an informational message only. No user action is required.
04/04/2011 11:41:36,spid6s,Unknown,Starting up database 'mssqlsystemresource'.
04/04/2011 11:41:36,spid6s,Unknown,SQL Trace ID 1 was started by login "sa".
04/04/2011 11:41:36,spid6s,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.
04/04/2011 11:41:36,spid6s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
04/04/2011 11:41:35,spid6s,Unknown,Starting up database 'master'.
04/04/2011 11:41:35,Server,Unknown,Node configuration: node 1: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
04/04/2011 11:41:35,Server,Unknown,Node configuration: node 0: CPU mask: 0x00000000000000f0 Active CPU mask: 0x00000000000000f0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
04/04/2011 11:41:35,Server,Unknown,Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
04/04/2011 11:41:35,Server,Unknown,Detected 8 CPUs. This is an informational message; no user action is required.
04/04/2011 11:41:35,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
04/04/2011 11:41:34,Server,Unknown,Registry startup parameters: <nl/> -d E:\SQL Data\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf<nl/> -e E:\SQL Data\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG<nl/> -l E:\SQL Data\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
04/04/2011 11:41:34,Server,Unknown,This instance of SQL Server last reported using a process ID of 4084 at 04/04/2011 11:38:49 (local) 04/04/2011 10:38:49 (UTC). This is an informational message only; no user action is required.
04/04/2011 11:41:34,Server,Unknown,Logging SQL Server messages in file 'E:\SQL Data\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
04/04/2011 11:41:34,Server,Unknown,Authentication mode is WINDOWS-ONLY.
04/04/2011 11:41:34,Server,Unknown,System Manufacturer: 'HP'<c/> System Model: 'ProLiant ML350 G6'.
04/04/2011 11:41:34,Server,Unknown,Server process ID is 1372.
04/04/2011 11:41:34,Server,Unknown,All rights reserved.
04/04/2011 11:41:34,Server,Unknown,(c) 2005 Microsoft Corporation.
04/04/2011 11:41:34,Server,Unknown,Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) <nl/> Sep 16 2010 19:43:16 <nl/> Copyright (c) 1988-2008 Microsoft Corporation<nl/> Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-14 : 05:53:39

Post your query

Raghu' S

Robert London
Starting Member

26 Posts

Posted - 2011-04-14 : 06:30:52

Raghu's, thank you very much for your help. I have managed to figure the problem out. This was a new server install a week ago and windows updates was on automatic so shutdown the server to install the updates!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources