Local variable in Grant statemant

Question

Post reply

Local variable in Grant statemant

Vishal Prajapati-253528

Ten Centuries

Points: 1142
More actions
June 2, 2006 at 7:43 am

#172877

Hi,
Declare @user varchar(200)
set @user = 'mymachine\vishal'
GRANT EXECUTE ON mytest_sp TO @user
GO
I am trying to run the above script it given me the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@user'.
Please help....

Kindest Regards,
Vishal Prajapati
DBA at Extentia Infromation Technology

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

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

stewart noble SSCrazy Points: 2009 More actions · Answer 1

this should do the trick

declare @exec varchar(500),

@user varchar(200)

set @user = 'mymachine\vishal'

select @exec = GRANT EXECUTE ON mytest_sp TO ' + @user

exec (@exec)

Vishal Prajapati-253528 Ten Centuries Points: 1142 More actions · Answer 2

Hi,

I have this solution but mainly I want to know whatz the reason????

Why it below block of code gives error.

Declare @user varchar(200)

set @user = 'mymachine\vishal'

GRANT EXECUTE ON mytest_sp TO @user

GO

Please give the reason...

Kindest Regards,

Vishal Prajapati

DBA at Extentia Infromation Technology

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

The message you got back is a general syntax error. '@user' is not a valid user name, so the parser rejected your batch. You must specify the user in the GRANT statement, not a local variable name.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

You also might want to search the forums here for 'dynamic sql'. There are lots of discussions on this here. This way you will have a better understanding.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."