Logon failed for login due to trigger execution

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
2
down vote

favorite

I have been trying to login using one of my sql server logins but I am getting the error message below:

General Error Message

enter image description here

Copy Message Text

TITLE: Connect to Server
------------------------------

Cannot connect to MY_SERVER.

------------------------------
ADDITIONAL INFORMATION:

Logon failed for login 'cola' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

For help, click: http://go.microsoft.com/fwlink?> ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Advanced Information

enter image description here

I am aware of the question below but it is slightly different and I have tried everything that is said there and it did not work for me, that's why I am putting this question here:

Ã¢Â€ÂœLogon failed for login 'sa' due to trigger execution.Ã¢Â€Â When No SA Login Triggers Defined

This question here is very similar too:

And from this one I got the following info:

1 'Account is locked out'
2 'User id is not valid'
3-4 'Undocumented'
5 'User id is not valid'
6 'Undocumented'
7 'The login being used is disabled'
8 'Incorrect password'
9 'Invalid password'
10 'Related to a SQL login being bound to Windows domain password policy enforcement.
 See KB925744.'
11-12 'Login valid but server access failed'
16 'Login valid, but not permissioned to use the target database'
18 'Password expired'
27 'Initial database could not be found'
38 'Login valid but database unavailable (or login not permissioned)'

According to the information above I have the following issue:

Login valid but server access failed

Still no joy.

I tried to drop the login and re-create it, but encountered a
Login can not be dropped because it is in use.

I used the script below to find out where it is used, by whom, from where:

USE master
go
SELECT 
 sdes.session_id 
 ,sdes.login_time 
 ,sdes.last_request_start_time
 ,sdes.last_request_end_time
 ,sdes.is_user_process
 ,sdes.host_name
 ,sdes.program_name
 ,sdes.login_name
 ,sdes.status

 ,sdec.num_reads
 ,sdec.num_writes
 ,sdec.last_read
 ,sdec.last_write
 ,sdes.reads
 ,sdes.logical_reads
 ,sdes.writes

 ,sdest.DatabaseName 
 ,sdest.ObjName
 ,sdes.client_interface_name
 ,sdes.nt_domain
 ,sdes.nt_user_name
 ,sdec.client_net_address
 ,sdec.local_net_address
 ,sdest.Query
 ,KillCommand = 'Kill '+ CAST(sdes.session_id AS VARCHAR)
FROM sys.dm_exec_sessions AS sdes

INNER JOIN sys.dm_exec_connections AS sdec 
 ON sdec.session_id = sdes.session_id

CROSS APPLY (

 SELECT DB_NAME(dbid) AS DatabaseName
 ,OBJECT_NAME(objectid) AS ObjName
 ,COALESCE((
 SELECT TEXT AS [processing-instruction(definition)]
 FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) 
 FOR XML PATH('')
 ,TYPE
 ), '') AS Query

 FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)

 ) sdest
WHERE sdes.session_id <> @@SPID 
--ORDER BY sdes.last_request_start_time DESC

I then killed the 2 sessions, re-created the login, tried again, same error.

What am I missing?

edited 56 mins ago

hot2use

7,49541951

asked 1 hour ago

marcello miorelli

5,1911659123

1

So what's in sys.server_triggers? Is there a logon trigger that prevents logins or not? If there is, there's no particular reason to assume re-creating the login would change the conditions that the trigger uses to accept or reject the login -- that would require studying the contents of the trigger.
â€“Â Jeroen Mostert
1 hour ago

Also, aside from the trigger explicitly rejecting logins with an error, one of the most common failure modes of login triggers is failing to account for the fact that it's executing as the login, e.g. trying to insert something in an audit table that the login has no insert permission on (the usual fix is EXECUTE AS). Another popular failure mode is failing to account for the transaction isolation level (which is whatever the application has set it to be). All in all, they're not for the faint of heart.
â€“Â Jeroen Mostert
39 mins ago

Sorry about that. Instead of commenting I went straight ahead with answering the question. Didn't even realise you had comments flowing.
â€“Â hot2use
31 mins ago

@hot2use don't worry you did well and added extra info
â€“Â marcello miorelli
19 mins ago

add a commentÂ |Â

up vote
2
down vote

favorite

I have been trying to login using one of my sql server logins but I am getting the error message below:

General Error Message

enter image description here

Copy Message Text

TITLE: Connect to Server
------------------------------

Cannot connect to MY_SERVER.

------------------------------
ADDITIONAL INFORMATION:

Logon failed for login 'cola' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

For help, click: http://go.microsoft.com/fwlink?> ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Advanced Information

enter image description here

I am aware of the question below but it is slightly different and I have tried everything that is said there and it did not work for me, that's why I am putting this question here:

Ã¢Â€ÂœLogon failed for login 'sa' due to trigger execution.Ã¢Â€Â When No SA Login Triggers Defined

This question here is very similar too:

And from this one I got the following info:

1 'Account is locked out'
2 'User id is not valid'
3-4 'Undocumented'
5 'User id is not valid'
6 'Undocumented'
7 'The login being used is disabled'
8 'Incorrect password'
9 'Invalid password'
10 'Related to a SQL login being bound to Windows domain password policy enforcement.
 See KB925744.'
11-12 'Login valid but server access failed'
16 'Login valid, but not permissioned to use the target database'
18 'Password expired'
27 'Initial database could not be found'
38 'Login valid but database unavailable (or login not permissioned)'

According to the information above I have the following issue:

Login valid but server access failed

Still no joy.

I tried to drop the login and re-create it, but encountered a
Login can not be dropped because it is in use.

I used the script below to find out where it is used, by whom, from where:

USE master
go
SELECT 
 sdes.session_id 
 ,sdes.login_time 
 ,sdes.last_request_start_time
 ,sdes.last_request_end_time
 ,sdes.is_user_process
 ,sdes.host_name
 ,sdes.program_name
 ,sdes.login_name
 ,sdes.status

 ,sdec.num_reads
 ,sdec.num_writes
 ,sdec.last_read
 ,sdec.last_write
 ,sdes.reads
 ,sdes.logical_reads
 ,sdes.writes

 ,sdest.DatabaseName 
 ,sdest.ObjName
 ,sdes.client_interface_name
 ,sdes.nt_domain
 ,sdes.nt_user_name
 ,sdec.client_net_address
 ,sdec.local_net_address
 ,sdest.Query
 ,KillCommand = 'Kill '+ CAST(sdes.session_id AS VARCHAR)
FROM sys.dm_exec_sessions AS sdes

INNER JOIN sys.dm_exec_connections AS sdec 
 ON sdec.session_id = sdes.session_id

CROSS APPLY (

 SELECT DB_NAME(dbid) AS DatabaseName
 ,OBJECT_NAME(objectid) AS ObjName
 ,COALESCE((
 SELECT TEXT AS [processing-instruction(definition)]
 FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) 
 FOR XML PATH('')
 ,TYPE
 ), '') AS Query

 FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)

 ) sdest
WHERE sdes.session_id <> @@SPID 
--ORDER BY sdes.last_request_start_time DESC

I then killed the 2 sessions, re-created the login, tried again, same error.

What am I missing?

edited 56 mins ago

hot2use

7,49541951

asked 1 hour ago

marcello miorelli

5,1911659123

1

So what's in sys.server_triggers? Is there a logon trigger that prevents logins or not? If there is, there's no particular reason to assume re-creating the login would change the conditions that the trigger uses to accept or reject the login -- that would require studying the contents of the trigger.
â€“Â Jeroen Mostert
1 hour ago

Also, aside from the trigger explicitly rejecting logins with an error, one of the most common failure modes of login triggers is failing to account for the fact that it's executing as the login, e.g. trying to insert something in an audit table that the login has no insert permission on (the usual fix is EXECUTE AS). Another popular failure mode is failing to account for the transaction isolation level (which is whatever the application has set it to be). All in all, they're not for the faint of heart.
â€“Â Jeroen Mostert
39 mins ago

Sorry about that. Instead of commenting I went straight ahead with answering the question. Didn't even realise you had comments flowing.
â€“Â hot2use
31 mins ago

@hot2use don't worry you did well and added extra info
â€“Â marcello miorelli
19 mins ago

add a commentÂ |Â

up vote
2
down vote

favorite

I have been trying to login using one of my sql server logins but I am getting the error message below:

General Error Message

enter image description here

Copy Message Text

TITLE: Connect to Server
------------------------------

Cannot connect to MY_SERVER.

------------------------------
ADDITIONAL INFORMATION:

Logon failed for login 'cola' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

For help, click: http://go.microsoft.com/fwlink?> ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Advanced Information

enter image description here

I am aware of the question below but it is slightly different and I have tried everything that is said there and it did not work for me, that's why I am putting this question here:

Ã¢Â€ÂœLogon failed for login 'sa' due to trigger execution.Ã¢Â€Â When No SA Login Triggers Defined

This question here is very similar too:

And from this one I got the following info:

1 'Account is locked out'
2 'User id is not valid'
3-4 'Undocumented'
5 'User id is not valid'
6 'Undocumented'
7 'The login being used is disabled'
8 'Incorrect password'
9 'Invalid password'
10 'Related to a SQL login being bound to Windows domain password policy enforcement.
 See KB925744.'
11-12 'Login valid but server access failed'
16 'Login valid, but not permissioned to use the target database'
18 'Password expired'
27 'Initial database could not be found'
38 'Login valid but database unavailable (or login not permissioned)'

According to the information above I have the following issue:

Login valid but server access failed

Still no joy.

I tried to drop the login and re-create it, but encountered a
Login can not be dropped because it is in use.

I used the script below to find out where it is used, by whom, from where:

USE master
go
SELECT 
 sdes.session_id 
 ,sdes.login_time 
 ,sdes.last_request_start_time
 ,sdes.last_request_end_time
 ,sdes.is_user_process
 ,sdes.host_name
 ,sdes.program_name
 ,sdes.login_name
 ,sdes.status

 ,sdec.num_reads
 ,sdec.num_writes
 ,sdec.last_read
 ,sdec.last_write
 ,sdes.reads
 ,sdes.logical_reads
 ,sdes.writes

 ,sdest.DatabaseName 
 ,sdest.ObjName
 ,sdes.client_interface_name
 ,sdes.nt_domain
 ,sdes.nt_user_name
 ,sdec.client_net_address
 ,sdec.local_net_address
 ,sdest.Query
 ,KillCommand = 'Kill '+ CAST(sdes.session_id AS VARCHAR)
FROM sys.dm_exec_sessions AS sdes

INNER JOIN sys.dm_exec_connections AS sdec 
 ON sdec.session_id = sdes.session_id

CROSS APPLY (

 SELECT DB_NAME(dbid) AS DatabaseName
 ,OBJECT_NAME(objectid) AS ObjName
 ,COALESCE((
 SELECT TEXT AS [processing-instruction(definition)]
 FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) 
 FOR XML PATH('')
 ,TYPE
 ), '') AS Query

 FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)

 ) sdest
WHERE sdes.session_id <> @@SPID 
--ORDER BY sdes.last_request_start_time DESC

I then killed the 2 sessions, re-created the login, tried again, same error.

What am I missing?

edited 56 mins ago

hot2use

7,49541951

asked 1 hour ago

marcello miorelli

5,1911659123

I have been trying to login using one of my sql server logins but I am getting the error message below:

General Error Message

enter image description here

Copy Message Text

TITLE: Connect to Server
------------------------------

Cannot connect to MY_SERVER.

------------------------------
ADDITIONAL INFORMATION:

Logon failed for login 'cola' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

For help, click: http://go.microsoft.com/fwlink?> ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Advanced Information

enter image description here

I am aware of the question below but it is slightly different and I have tried everything that is said there and it did not work for me, that's why I am putting this question here:

Ã¢Â€ÂœLogon failed for login 'sa' due to trigger execution.Ã¢Â€Â When No SA Login Triggers Defined

This question here is very similar too:

And from this one I got the following info:

1 'Account is locked out'
2 'User id is not valid'
3-4 'Undocumented'
5 'User id is not valid'
6 'Undocumented'
7 'The login being used is disabled'
8 'Incorrect password'
9 'Invalid password'
10 'Related to a SQL login being bound to Windows domain password policy enforcement.
 See KB925744.'
11-12 'Login valid but server access failed'
16 'Login valid, but not permissioned to use the target database'
18 'Password expired'
27 'Initial database could not be found'
38 'Login valid but database unavailable (or login not permissioned)'

According to the information above I have the following issue:

Login valid but server access failed

Still no joy.

I tried to drop the login and re-create it, but encountered a
Login can not be dropped because it is in use.

I used the script below to find out where it is used, by whom, from where:

USE master
go
SELECT 
 sdes.session_id 
 ,sdes.login_time 
 ,sdes.last_request_start_time
 ,sdes.last_request_end_time
 ,sdes.is_user_process
 ,sdes.host_name
 ,sdes.program_name
 ,sdes.login_name
 ,sdes.status

 ,sdec.num_reads
 ,sdec.num_writes
 ,sdec.last_read
 ,sdec.last_write
 ,sdes.reads
 ,sdes.logical_reads
 ,sdes.writes

 ,sdest.DatabaseName 
 ,sdest.ObjName
 ,sdes.client_interface_name
 ,sdes.nt_domain
 ,sdes.nt_user_name
 ,sdec.client_net_address
 ,sdec.local_net_address
 ,sdest.Query
 ,KillCommand = 'Kill '+ CAST(sdes.session_id AS VARCHAR)
FROM sys.dm_exec_sessions AS sdes

INNER JOIN sys.dm_exec_connections AS sdec 
 ON sdec.session_id = sdes.session_id

CROSS APPLY (

 SELECT DB_NAME(dbid) AS DatabaseName
 ,OBJECT_NAME(objectid) AS ObjName
 ,COALESCE((
 SELECT TEXT AS [processing-instruction(definition)]
 FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) 
 FOR XML PATH('')
 ,TYPE
 ), '') AS Query

 FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)

 ) sdest
WHERE sdes.session_id <> @@SPID 
--ORDER BY sdes.last_request_start_time DESC

I then killed the 2 sessions, re-created the login, tried again, same error.

What am I missing?

sql-server sql-server-2016 security logins connections

edited 56 mins ago

hot2use

7,49541951

asked 1 hour ago

marcello miorelli

5,1911659123

edited 56 mins ago

hot2use

7,49541951

asked 1 hour ago

marcello miorelli

5,1911659123

edited 56 mins ago

hot2use

7,49541951

edited 56 mins ago

hot2use

7,49541951

edited 56 mins ago

hot2use

7,49541951

asked 1 hour ago

marcello miorelli

5,1911659123

asked 1 hour ago

marcello miorelli

5,1911659123

asked 1 hour ago

marcello miorelli

5,1911659123

1

So what's in sys.server_triggers? Is there a logon trigger that prevents logins or not? If there is, there's no particular reason to assume re-creating the login would change the conditions that the trigger uses to accept or reject the login -- that would require studying the contents of the trigger.
â€“Â Jeroen Mostert
1 hour ago

Also, aside from the trigger explicitly rejecting logins with an error, one of the most common failure modes of login triggers is failing to account for the fact that it's executing as the login, e.g. trying to insert something in an audit table that the login has no insert permission on (the usual fix is EXECUTE AS). Another popular failure mode is failing to account for the transaction isolation level (which is whatever the application has set it to be). All in all, they're not for the faint of heart.
â€“Â Jeroen Mostert
39 mins ago

Sorry about that. Instead of commenting I went straight ahead with answering the question. Didn't even realise you had comments flowing.
â€“Â hot2use
31 mins ago

@hot2use don't worry you did well and added extra info
â€“Â marcello miorelli
19 mins ago

add a commentÂ |Â

1

So what's in sys.server_triggers? Is there a logon trigger that prevents logins or not? If there is, there's no particular reason to assume re-creating the login would change the conditions that the trigger uses to accept or reject the login -- that would require studying the contents of the trigger.
â€“Â Jeroen Mostert
1 hour ago

Also, aside from the trigger explicitly rejecting logins with an error, one of the most common failure modes of login triggers is failing to account for the fact that it's executing as the login, e.g. trying to insert something in an audit table that the login has no insert permission on (the usual fix is EXECUTE AS). Another popular failure mode is failing to account for the transaction isolation level (which is whatever the application has set it to be). All in all, they're not for the faint of heart.
â€“Â Jeroen Mostert
39 mins ago

Sorry about that. Instead of commenting I went straight ahead with answering the question. Didn't even realise you had comments flowing.
â€“Â hot2use
31 mins ago

@hot2use don't worry you did well and added extra info
â€“Â marcello miorelli
19 mins ago

So what's in sys.server_triggers? Is there a logon trigger that prevents logins or not? If there is, there's no particular reason to assume re-creating the login would change the conditions that the trigger uses to accept or reject the login -- that would require studying the contents of the trigger.
â€“Â Jeroen Mostert
1 hour ago

Also, aside from the trigger explicitly rejecting logins with an error, one of the most common failure modes of login triggers is failing to account for the fact that it's executing as the login, e.g. trying to insert something in an audit table that the login has no insert permission on (the usual fix is EXECUTE AS). Another popular failure mode is failing to account for the transaction isolation level (which is whatever the application has set it to be). All in all, they're not for the faint of heart.
â€“Â Jeroen Mostert
39 mins ago

Sorry about that. Instead of commenting I went straight ahead with answering the question. Didn't even realise you had comments flowing.
â€“Â hot2use
31 mins ago

@hot2use don't worry you did well and added extra info
â€“Â marcello miorelli
19 mins ago

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
3
down vote

accepted

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error.

You might have previously had a logon trigger that evaluated if you were allowed to log in to the SQL Server or not. This trigger may have been removed or might produce wrong results which now does not allow anybody to log in to the SQL Server instance.

There are three possible workarounds:

1. Open Query Window Available

Search for the trigger via:

SELECT * FROM sys.server_triggers;

If you see a server level trigger that might be responsible for the login issues, then you can drop it with:

DROP TRIGGER triggername ON ALL SERVER;

Instead of dropping the trigger just disable it with:

DISABLE TRIGGER triggername ON ALL SERVER;

2. DAC Allowed; No Open Query Window Available

If you don't have an open Query Window, then you will have to log in to your SQL Server instance with a DAC (ADMIN) connection:

sqlcmd Ã¢Â€Â“S 127.0.0.1,1434

1434 is the default port for the DAC connection

You can then run the above queries from solution 1. to determine the trigger and then drop or disable the trigger.

3. No Open Query Window; No DAC Allowed

If you don't have an open Window and DAC has not been turned on, then you are going to have to stop the instance and start again using the following steps:

sqlserver.exe Ã¢Â€Â“c -m Ã¢Â€Â“f

This will allow you to start the instance in single user mode with minimal configuration and shorter startup time.

Then you can disable the triggers according to step 1. above.

answered 33 mins ago

hot2use

7,49541951

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218811%2flogon-failed-for-login-due-to-trigger-execution%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
3
down vote

accepted

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error.

There are three possible workarounds:

1. Open Query Window Available

Search for the trigger via:

SELECT * FROM sys.server_triggers;

If you see a server level trigger that might be responsible for the login issues, then you can drop it with:

DROP TRIGGER triggername ON ALL SERVER;

Instead of dropping the trigger just disable it with:

DISABLE TRIGGER triggername ON ALL SERVER;

2. DAC Allowed; No Open Query Window Available

If you don't have an open Query Window, then you will have to log in to your SQL Server instance with a DAC (ADMIN) connection:

sqlcmd Ã¢Â€Â“S 127.0.0.1,1434

1434 is the default port for the DAC connection

You can then run the above queries from solution 1. to determine the trigger and then drop or disable the trigger.

3. No Open Query Window; No DAC Allowed

If you don't have an open Window and DAC has not been turned on, then you are going to have to stop the instance and start again using the following steps:

sqlserver.exe Ã¢Â€Â“c -m Ã¢Â€Â“f

This will allow you to start the instance in single user mode with minimal configuration and shorter startup time.

Then you can disable the triggers according to step 1. above.

answered 33 mins ago

hot2use

7,49541951

add a commentÂ |Â

up vote
3
down vote

accepted

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error.

There are three possible workarounds:

1. Open Query Window Available

Search for the trigger via:

SELECT * FROM sys.server_triggers;

If you see a server level trigger that might be responsible for the login issues, then you can drop it with:

DROP TRIGGER triggername ON ALL SERVER;

Instead of dropping the trigger just disable it with:

DISABLE TRIGGER triggername ON ALL SERVER;

2. DAC Allowed; No Open Query Window Available

If you don't have an open Query Window, then you will have to log in to your SQL Server instance with a DAC (ADMIN) connection:

sqlcmd Ã¢Â€Â“S 127.0.0.1,1434

1434 is the default port for the DAC connection

You can then run the above queries from solution 1. to determine the trigger and then drop or disable the trigger.

3. No Open Query Window; No DAC Allowed

If you don't have an open Window and DAC has not been turned on, then you are going to have to stop the instance and start again using the following steps:

sqlserver.exe Ã¢Â€Â“c -m Ã¢Â€Â“f

This will allow you to start the instance in single user mode with minimal configuration and shorter startup time.

Then you can disable the triggers according to step 1. above.

answered 33 mins ago

hot2use

7,49541951

add a commentÂ |Â

up vote
3
down vote

accepted

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error.

There are three possible workarounds:

1. Open Query Window Available

Search for the trigger via:

SELECT * FROM sys.server_triggers;

If you see a server level trigger that might be responsible for the login issues, then you can drop it with:

DROP TRIGGER triggername ON ALL SERVER;

Instead of dropping the trigger just disable it with:

DISABLE TRIGGER triggername ON ALL SERVER;

2. DAC Allowed; No Open Query Window Available

If you don't have an open Query Window, then you will have to log in to your SQL Server instance with a DAC (ADMIN) connection:

sqlcmd Ã¢Â€Â“S 127.0.0.1,1434

1434 is the default port for the DAC connection

You can then run the above queries from solution 1. to determine the trigger and then drop or disable the trigger.

3. No Open Query Window; No DAC Allowed

If you don't have an open Window and DAC has not been turned on, then you are going to have to stop the instance and start again using the following steps:

sqlserver.exe Ã¢Â€Â“c -m Ã¢Â€Â“f

This will allow you to start the instance in single user mode with minimal configuration and shorter startup time.

Then you can disable the triggers according to step 1. above.

answered 33 mins ago

hot2use

7,49541951

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error.

There are three possible workarounds:

1. Open Query Window Available

Search for the trigger via:

SELECT * FROM sys.server_triggers;

If you see a server level trigger that might be responsible for the login issues, then you can drop it with:

DROP TRIGGER triggername ON ALL SERVER;

Instead of dropping the trigger just disable it with:

DISABLE TRIGGER triggername ON ALL SERVER;

2. DAC Allowed; No Open Query Window Available

If you don't have an open Query Window, then you will have to log in to your SQL Server instance with a DAC (ADMIN) connection:

sqlcmd Ã¢Â€Â“S 127.0.0.1,1434

1434 is the default port for the DAC connection

You can then run the above queries from solution 1. to determine the trigger and then drop or disable the trigger.

3. No Open Query Window; No DAC Allowed

If you don't have an open Window and DAC has not been turned on, then you are going to have to stop the instance and start again using the following steps:

sqlserver.exe Ã¢Â€Â“c -m Ã¢Â€Â“f

This will allow you to start the instance in single user mode with minimal configuration and shorter startup time.

Then you can disable the triggers according to step 1. above.

answered 33 mins ago

hot2use

7,49541951

answered 33 mins ago

hot2use

7,49541951

answered 33 mins ago

hot2use

7,49541951

answered 33 mins ago

hot2use

7,49541951

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Category

Random preview