Granting DB access inside trigger
-
Hi,
I have two DB running on same instance of MSDE. DB1 is third party production DB and DB2 custom for counting and tracking purposes.
I have two small problems regarding DB users and triggers.
First, I created trigger on one of the tables in DB1 to insert some info into DB2 and getting message that Server user "User1" is not a valid user in DB2. So, I granted access to DB2 for user1 (use DB2; exec sp_grantdbaccess 'User1'; exec sp_addrolemember CcTLogPublic, 'user1'). Is there any way to avoid it?
CREATE TRIGGER CreateTestLogRecord
ON DB1.dbo.Test
AFTER INSERT, UPDATE AS
<Bunch of sets >
INSERT INTO DB2.dbo.TestLog
(testLogID,patID, testID ….)
(select NEWID(),i.PatID,i.TstID…)
If no other way around, than I need to track users in DB1 and add same user in DB2. I tried to create another trigger on INSERT user in DB1, but USE not allowed inside transaction/trigger.
CREATE TRIGGER ADDTestLogUser
ON DB1.dbo.Usr AFTER INSERT
AS
DECLARE @UserN nvarchar(128)
set @UserN = (select UsrName from inserted)
USE DB2 –this is a problem !!!! USE not allowed inside transaction
exec sp_grantdbaccess @UserN
exec sp_addrolemember CcTLogPublic, @UserN
I even tried to do it via command prompt, but user login not exists yet in DB, because I am calling this command from trigger
set @CmdStr = 'osql.exe -E -Q "use ccwtestlog; exec sp_grantdbaccess '''+ @UserN+'''; exec sp_addrolemember CcTLogPublic, '''+@UserN + '''"'
EXEC master.dbo.xp_cmdshell @CmdStr
Any help and suggestions appreciated
Thank you,
Alex
-
First things first:
Adding users on the Flight is a BAD practice!!
Now if you still insist then you should do it ahead of time (before the trigger fires) not at trigger time!
* Noel
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply