The error is as shown in the figure:
Figure I
If you do not perform a login trigger well, you will cause the login to fail.
For example, if you create this trigger, you can design the following code to achieve the goal of failure.
Copy Code code as follows:
CREATE TRIGGER Badlogontrigger on ' All SERVER ' for LOGON
As
BEGIN
INSERT into BadDB.dbo.SomeTable VALUES (' Test ');
End;
Go
No database is called BADDB, which means that there is not a single table called SomeTable in BADDB. As a result, any normal attempt to log on to the server will fail because the trigger involves a nonexistent object. To correct this problem, you also need to:
The connection has the appropriate permissions, using an existing build connection.
Connect SQL Server using the dedicated Administrator connection (DAC).
If one of your existing connections can remove triggers or make them unavailable, use an existing connection to correct the problem. However, in some cases, your connection does not have this function, then you need to rely on a dedicated administrator connection.
By default, this dedicated administrator connection can only be used on the local server. This means that you need to connect by logging on to the local computer or using another way, such as Remote Desktop. Once you have logged in, you can use SQLCMD or ssms.
If you use SQLCMD, you need to connect by specifying a-a switch through the dedicated administrator connection. If you connect via SSMs, make sure to connect by specifying admin: In front of the server name, as shown in Figure Ii.
Figure II
This behavior occurs because SQL Server minimizes connectivity checks and resources through a dedicated administrator connection. This method gives the database administrator a "backdoor" when one or more processes consume a SQL Server and the login does not work properly. When connected over a DAC, the one thing SQL Server does not do is execute any login triggers. So you can use the DAC and you won't be hindered by this bad trigger. Then, if necessary, you can make the trigger unavailable or remove the trigger.
For example, once connected through the DAC, I can execute the following command to completely get rid of this trigger:
Copy Code code as follows:
DROP TRIGGER Badlogontrigger on the all SERVER;
Go
