Category Archives: Notes

All Articles

Error 18456, Severity 14: Login Failed States

2 Replies

Error 18456, Severity 14: Login Failed States
Login Failed states summary
State	Description
1	Only state returned in SQL 2000. Server is in Single-User Mode (2005).
2 and 5	Invalid User Id
6	Attempt to use a windows login name with SQL Authentication
7	Login disabled and password mismatch
8	Password mismatch
9	Invalid Password
10	Read Ref Link #2
11 and 12	Valid login but server access failure; Default Database access failure. Or Initial database connection failure in connection string.
13	SQL Server service paused
16	User doesn’t have permission to target database
18	Change password required
23	Server in process of shutting down, and user attempted to login.
27	Initial database could not be determined for session
38	Initial database could not be determined for session (SQL 2008)
40	Default database could not be accessed (SQL 2008)

How to Alter a SQL Server Database as Single User Mode and as Multi User Mode

You can use the SQL Server Enterprise Manager for SQL Server 2000 to set a database to a single user mode or to multi user mode. Similarly, SQL Server Management Studio can be used for SQL Server 2005 for changing a database to single user mode or to multi user mode. Also you can alter the database access mode by using sql commands like ALTER DATABASE and sp_dboption.

ALTER DATABASE [MyDBName] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [MyDBName] SET SINGLE_USER WITH NO_WAIT

or

EXEC sp_dboption ‘MyDBName’, ‘single user’, ‘false’

EXEC sp_dboption ‘MyDBName’, ‘single user’, ‘true’

Happy Republic Day

7 Replies

republic-day

Find the SQL Server Service Startup Time

Find the SQL Server Service Startup Time

Many times for collecting or analyzing performance counter from the different DMV we need to collect the data of many days, so at that it is necessary to find the SQL Server Service startup time. We can find it as below.

1) Every time when SQL Server restarts, It creates the “TempDB” so from the TempDB creation time you can find SQL Server startup time.

SELECT CREAT_DATE FROM SYS.DATABASES WHERE NAME = ‘TEMPDB’

2) If you error log is not flushed you can use the XP_ReadErrorlog and from there you can find the SQL Server startup time

3) We can also find it using sys.dm_exec_requests DMV for startup time

4) We can also find it from Master..SysProcesses system table by analyzing the login time for the System Processes (for i.e. LAZYWRITER)

Execute As in SQL Server 2005

When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. This will help database administrator to check the different user permissions. `Execute As` is also very helpful when a user wants to execute a stored procedure in the context of another user.

Syntax
Execute as user = ‘SQL_JShah’
select * from CompanyProducts.Products