Using Variables with GRANT and DENY in SQL Server 2005

Question

In SQL Server 2005, how can you use a username variable for GRANT or DENY permissions for objects that have a database? I tried:

DECLARE @username varchar(30)
SET @username = 'DOMAIN\UserName'
GRANT SELECT ON [mytable] TO @username
GRANT UPDATE([field one], [field two], [field three]) ON [mytable] TO @username

I get Incorrect syntax near '@username', so I wrapped it in [and]

GRANT SELECT ON [mytable] TO [@username]

However, this leads to Cannot find the user '@username', because it does not exist or you do not have permission. How can I do this without entering a username for each operator? I want to do this to reduce the chance of any typos (which could lead to the wrong user getting permissions)

Answer 1

sql, EXEC PRINT, , ​​ quotename,

 DECLARE @username varchar(30)
SET @username = 'DOMAIN\UserName'

SET @username = quotename(@username)

exec  ('GRANT SELECT ON [mytable] TO ' + @username )
exec ('GRANT UPDATE([field one], [field two], [field three]) ON [mytable] TO ' + @username )

Answer 2

SQL ?

DECLARE @sql VARCHAR(2000)
SET @sql = 'GRANT SELECT ON [mytable] TO ' + @username
EXEC @sql

Answer 3

By updating Yoopergeek's answer , you could

DECLARE @sql VARCHAR(2000)
SET @sql = 'GRANT SELECT ON [mytable] TO [' + Replace(@username, '[', '\[') + ']' + Char(13) + Char(10)
SET @sql = 'GRANT UPDATE([field one], [field two], [field three]) ON [mytable] TO [' + Replace(@username, '[', '\[') + ']'
PRINT @sql
EXEC @sql