Post reply

Local variable in Grant statemant

  • 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

  • June 2, 2006 at 9:18 am

    #641474

    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)

  • June 2, 2006 at 9:43 am

    #641484

    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

  • June 5, 2006 at 8:14 am

    #641723

    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.

     

     

     

  • June 5, 2006 at 10:55 am

    #641790

    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."

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

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