How to fix Logon trigger issue

Question

How to fix Logon trigger issue

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Login triggers fire early in the connection process, before the switch to the requested DB happens. That's why you need the 3-part table name and why you only see master.

As for the login name... ORIGINAL_LOGIN() is the function you want there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

BBSR SSCommitted Points: 1921 More actions · Answer 2

thank you for all the help. i am getting the data in the table now but as you said it still shows the db name as "master" and not the user database. I have one more question, do i have to give "Insert" right on the Audit table to all the users? because if i don't i get the same error message and only sys admin is able to log in but when i give Insert right on that table to all users then everyone is able to log in.

BBSR SSCommitted Points: 1921 More actions · Answer 3

hi again ,

is there any way to get the user database name in the db_name column instead of "master"?

thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Rather use impersonation and just have that login having the permissions. Shouldn't be SA though. I don't think you can get the DB in a login trigger, it fires too early in the login process.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

BBSR SSCommitted Points: 1921 More actions · Answer 5

thank you again but i got the user db name. i used ORIGINAL_DB_NAME() and it returned me the user database name in the db column.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Im also facing same problem, SQL server does not allowed for login any user.. also DAC connection is not working even enabled... so I have daily backup of master database...Just restored perivious master database backup..then my problem is resolved...if backup is not availale need to rebuild master db using setup CD.