Trigger cannot create users

Question

Post reply

Trigger cannot create users

mngong

SSCertifiable

Points: 6167
More actions
August 7, 2006 at 2:01 pm

#61704

I have a table called log.And each time a user is added to the application the user is added to the log able.
I want this user to be able to be added to the database and have put together this.
Can someone tell me what I am doing wrong??
CREATE TRIGGER Create_Login
ON [log]
FOR INSERT
AS
BEGIN
DECLARE @log_id varchar(20)
DECLARE @@sqlstring varchar(200)
DECLARE @@sqlstring2 varchar(200)
DECLARE @@sqlstring3 varchar(200)
SELECT @log_id = RTRIM(log_id) FROM inserted ins
Print @log_id
SET @@sqlstring = ' [CRON\'+ @log_id +']'--+' GO'
print @@sqlstring
--insert into triggertable(log_id)values(@@sqlstring)
--IF @log_id NOT IN (select log_id from log)
BEGIN
SET @sqlstring = 'sp_grantlogin'+' [CRON\'+ @log_id +']'--+' GO'
EXEC Create_QW_Logins @sqlstring
-- Adds the login capability
SET @sqlstring = 'sp_grantlogin'+' [CRON\'+ @log_id +']'--+' GO'
Print @sqlstring
EXEC ( @sqlstring)
Adds permissions on the database
SET @sqlstring2 = 'sp_grantdbaccess'+' [CRON\'+ @log_id +']'--+ ' GO'
EXEC ( @sqlstring2)
-- Adds users to the db_owner role membership
SET @sqlstring2 = 'sp_addrolemember' + ' ' + '"db_owner",' +
'"Cron\"'+ @log_id +'"' + --' GO'
EXEC @sqlstring3
-- END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
Insert into [log]
values('jteague')
Delete from log
where log_id like ('jteaque')

Viewing 6 posts - 1 through 5 (of 5 total)

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

Bhushan Default port Points: 1427 More actions · Answer 1

Check the variable declaration. I think you are using @@ instead of @ for variable.

mngong SSCertifiable Points: 6167 More actions · Answer 2

I have done that and little changed .The problem seems to be sp_grantlogin cannot be run inside begin and end...Not sure how to go about it

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 3

You could shedule a job for this that reads from the log tables and adds the users.

mngong SSCertifiable Points: 6167 More actions · Answer 4

The proper sql to be run so that the job will succeed is my problem and that is what help is needed with

Thanks for your time Jo

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 5

My testing procedure which works. You only have to modify the window login section. With a simple cursor you can fetch each login & execute the stored procedure.

CREATE PROCEDURE dbo.UCreate_QW_Logins

(

@Log_id nvarchar(150)

)

AS

SET NOCOUNT ON

DECLARE @User nvarchar(250)

DECLARE @statement nvarchar(500)

DECLARE @Testpassword varchar(500)

SET @User= ' [CRON\'+ @log_id +']'

SET @User=@Log_id

SET @Testpassword='abc'

--add windowslogin to server

--SET @statement= 'sp_grantlogin '+@User

--EXECUTE master.dbo.sp_executesql @statement

--add sql server login

SET @statement='sp_addlogin '+@User+ ','+@Testpassword

EXECUTE master.dbo.sp_executesql @statement

--Adds permissions on the database

SET @statement = 'sp_grantdbaccess '+@User

EXECUTE master.dbo.sp_executesql @statement

-- Adds users to the db_owner role membership

SET @statement= 'sp_addrolemember ' + ' ' + 'db_owner,' +@User

EXECUTE master.dbo.sp_executesql @statement

--if all succesfull

delete from dbo.log where log_id=@Log_id

---end of stored proc

'reading log Insensitive for snapshot

DECLARE @newuser nvarchar(150)

DECLARE curNewUsers INSENSITIVE FORWARD_ONLY CURSOR

FOR SELECT log_id from Log

OPEN curNewUsers

FETCH NEXT FROM curNewUsers INTO @newuser

WHILE @@FETCH_STATUS=0

BEGIN

EXECUTE dbo.UCreate_QW_Logins @newuser

FETCH NEXT FROM curNewUsers INTO @newuser

END

CLOSE curNewUsers

DEALLOCATE curNewUsers