March 19, 2004 at 8:48 am
I am creating a SP as below but the parameter will not pass to all 3 processes. If I remove the last 2 processes it will work. Why it this and how can it be fixed? thanks
create procedure AddLogin @LoginID varchar(20)
as
exec sp_grantdbaccess @loginID
go
GRANT all
ON Engagement
TO @loginID
go
GRANT all
ON Conflict
TO @loginID
March 19, 2004 at 9:25 am
I havenot tried out this but how about something like this
create procedure AddLogin @LoginID varchar(20)
as
declare @sql varchar(100)
exec sp_grantdbaccess @loginID
set @sql='GRANT all
ON Engagement
TO ' + @loginID
exec (@sql)
set @sql ='GRANT all
ON Conflict
TO ' + @loginID
exec (@sql)
But BEWARE of the Curses of DYNAMIC SQL
Thanks
Prasad Bhogadi
www.inforaise.com
March 19, 2004 at 10:53 am
The stored procedure AddLogin will only have the exec of the sp_grantdbaccess @loginID line, because the GO marks the end of the sp.
The other two commands (grants) are not part of the sp you have created, and also, will fail because you will have to declare the variable @LoginId twice after both GO.
The script posted by Prasad Bhogadi should work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply