Microsoft SQL Server, Error: 17892

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 2005 Forums

Transact-SQL (2005)

Microsoft SQL Server, Error: 17892

Author

Topic

sathishmangunuri
Starting Member

32 Posts

Posted - 2010-12-28 : 06:13:27

I have created logon trigger. without dropping trigger i closed the connection.when i tried to login ,i am getting following error.

Logon failed for login ‘corp\smangunuri’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

i tried to login through sqlcmd also but not able login could any one help me please.
c:>sqlcmd -S serverName -E -A not able to connect.

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 06:32:56

Can you post the trigger script ?

PBUH

sathishmangunuri
Starting Member

32 Posts

Posted - 2010-12-28 : 06:47:23

quote:
Originally posted by Sachin.Nand

Can you post the trigger script ?

PBUH

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = 'S@tsanvij13' ,
CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT count(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 1
ROLLBACK;
END;

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 07:11:50

Did you try this ?

http://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources