Enabling Trigger when specific user will logOn..How?

Question

Enabling Trigger when specific user will logOn..How?

Noman Tariq

SSCommitted

Points: 1750
More actions
July 30, 2009 at 4:57 am

#208846

Hi All,
I am trying to do a simple thing but unable to get it through.
Problem:
I have a table in MyDB1, it also has a trigger which just sets the newid() after on every modification.
but i want that this trigger will only be enabled when a specific user 'TestUser' will logged on to that Database.
I am trying to do this by using LOGON triggers but still no success.
if anyone can help me in this regard.
Thanks,
Noman

Viewing 7 posts - 1 through 7 (of 7 total)

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

SQLEgan SSC Veteran Points: 204 More actions · Answer 1

Noman Tariq (7/30/2009)
Hi All,
I am trying to do a simple thing but unable to get it through.
Problem:
I have a table in MyDB1, it also has a trigger which just sets the newid() after on every modification.
but i want that this trigger will only be enabled when a specific user 'TestUser' will logged on to that Database.
I am trying to do this by using LOGON triggers but still no success.
if anyone can help me in this regard.
Thanks,
Noman

Can you not put some logic into your trigger along the lines of

if database_principal_id() = database_principal_id('testuser')

--insert your newid

else

do nothing.

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

Do you want the trigger to fire only for a specific user, or do you want the trigger to fire for everyone whenever a certain user is logged in?

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

Lowell SSC Guru Points: 323522 More actions · Answer 3

i was going to suggest teh same thing Paul suggested...forget trying to enable/disable a trigger, simple put an IF statment to check for that specific user;

here's a handy script to show you some of the functions you can use:

SELECT USER_ID() AS [USER_ID]

, USER_NAME() AS [USER_NAME]

, SUSER_ID() AS [SUSER_ID]

, SUSER_SNAME() AS [SUSER_SNAME]

, database_principal_id() as [database_principal_id]

, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]

, IS_MEMBER('db_owner') AS [Is_DB_owner]

, IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin]

, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]

, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Noman Tariq SSCommitted Points: 1750 More actions · Answer 4

Thanks Gail,

Actually i have two servers one is production and other is backup. and all our application are using production db with a specific user e.g., 'AppUser', if production db will gets down application will connect to backup db by using the same user 'AppUser' and my backup db having triggers on tables which sets a flag as a newid().

so i want when AppUser will connect to backup db a trigger will fire and it enables all my triggers on tables in backup db.

so far consider one table and having one trigger on it.

i want this login in LOGON trigger on server. is it possible??

Thanks,

Noman

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

Should be possible, I can't think of a reason why it wouldn't. What have you tried and what problems are you having.

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

Noman Tariq SSCommitted Points: 1750 More actions · Answer 6

yes it sounds like very simple but still facing login problem when i applied this trigger.

i am creating a trigger like this:

-------------------------------------------------

create TRIGGER test

ON ALL SERVER

FOR LOGON

AS

BEGIN

if (original_login() = 'AppUser')

enable trigger LogSubscriberModifications on users

END;

-------------------------------------------------

it applied successfully but when i tried to login from AppUser, a message comes: login failed due to trigger execution... change database context to db_name...

dont know why this is. i have set the rights and permission of that user again on that DB.

This is the rough draft of my trigger and logic. i will add more in it if it works fine.

Enabling Trigger when specific user will logOn..How?

Cookies on SQLServerCentral