GRANT SELECT QUERY and SP HELP

[dfwcharles]

I want to create a sp to grant select rights on certain tables for certain users. I created the following query, but I get error messages. I have tried different data types with qoutes and without, but always seem to get the same results. Any ideas?

Here's my test query:

declare @name SYSNAME (128)
set @name = CHARLES
grant select on TABLE1 to @NAME
grant select on TABLE2 to @NAME
grant select on TABLE3 to @NAME
grant select on TABLE4 to @NAME

This is what is returned:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@NAME'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@NAME'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@NAME'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@NAME'.

Thanks

 

[tlbroadbent]

You cannot use variables in this statement. You will have to use the Execute command if you want to use a variable in the SQL statement.

declare @name sysname
set @name = 'CHARLES'

Exec('grant select on TABLE1 to '+@NAME) Terry L. Broadbent - DBA
Computing Links:

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

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