Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How to fix Logon trigger issue Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 3:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 8:07 AM
Points: 56, Visits: 168
I changed the trigger but got the same login error when I tried to log in and the SQL Server error log also has the same error.

but I found out one thing.

if I create the table in master database and then create the trigger. it works fine. I am able to log into the user database and I believe other user's will also be able to log in

but when I run a select on the Audit table, the DB_NAME column only shows master and not the user database.
Post #1491981
Posted Thursday, September 5, 2013 3:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 8:07 AM
Points: 56, Visits: 168
the sql server error log shows the same error even after creating the revised trigger.

but if i create the Audit table in the master database and then it works fine. i am able to log in and i believe all other users will be able to do the same but when i run a select statement on that Audit table i see only master listed in the DB_NAME column and not the user database.
Post #1491985
Posted Thursday, September 5, 2013 4:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:43 PM
Points: 40,411, Visits: 36,861
Then...

INSERT  INTO <database name>.<schema_name>.Audit_Log...




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1491987
Posted Thursday, September 5, 2013 4:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 8:07 AM
Points: 56, Visits: 168
i got the same error message after creating the revised trigger
Post #1491990
Posted Thursday, September 5, 2013 4:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 8:07 AM
Points: 56, Visits: 168
changing the insert statement in the trigger worked but why in the SYSTEM_USER column and the DB_NAME column I see only sa and master respectively.

i clicked options and changed the database to the users database and then logged in but when i select the Audit table still it shows up as "master" in the DB_NAME column and "sa" in the SYSTEM_USER column

can you please help..
Post #1491996
Posted Friday, September 6, 2013 1:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
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

Post #1492069
Posted Friday, September 6, 2013 8:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 8:07 AM
Points: 56, Visits: 168
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.
Post #1492255
Posted Friday, September 6, 2013 9:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 8:07 AM
Points: 56, Visits: 168
hi again ,

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

thanks
Post #1492290
Posted Friday, September 6, 2013 9:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
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

Post #1492297
Posted Friday, September 6, 2013 9:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 8:07 AM
Points: 56, Visits: 168
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.
Post #1492312
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse