Using sp_addrolemember within a Trigger

[JoseC]

I am trying to automatically addrolemember rights to a user when their security clearance is set. I am trying the following Trigger.

----------------------------------------------------------
CREATE TRIGGER securityupdate ON MyTable
FOR UPDATE
AS

DECLARE @login varchar(50), @UPDsecurity int

SELECT @login=updated.usrname, @UPDsecurity=updated.sec FROM Inserted updated

IF @UPDsecurity = '1'
BEGIN
exec MyDB.dbo.sp_addrolemember
[db_accessadmin],
@login
exec MyDB.dbo.sp_addrolemember
[db_securityadmin],
@login
END
ELSE
BEGIN
exec MyDB.dbo.sp_droprolemember
[db_accessadmin],
@login
exec MyDB.dbo.sp_droprolemember
[db_securityadmin],
@login
END
----------------------------------------------------------

I get the following error when I change the table value:
[Microsoft][ODBC SQL Server Driver][SQL Server]The GRANT statement is not allowed within a trigger.


Did I call the proper SPROCs??
Thanks!

 

[tlbroadbent]

You called the correct procedures but you cannot execute a Grant statement from a Trigger. (See "Create Tirgger" in SQL BOL.) The procs you call execute GRANT statements. Even a neseted Grant is not alowed in a Trigger.

You'll need to devise a different way to assign the user to a role. One way to do this is to write a record to a transaction table and have a scheduled job execute the procedure. Terry L. Broadbent - DBA

http://tlbroadbent.home.attbi.com/index.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers."