Using variables with GRANT and DENY in SQL Server 2005

Question

In SQL Server 2005 how can you use a variable for the username to GRANT or DENY permissions to objects withing the database? I have 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 then I wrapped it in [ and ]

GRANT SELECT ON [mytable] TO [@username]

However this then results in Cannot find the user '@username', because it does not exist or you do not have permission. How can I do this without having to type out the username for each statement? I want to do this to reduce chances of any typo's (which could result in the wrong user getting permissions set)

Answer 1

Will using dynamic SQL work in this case?

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

Answer 2

you need dynamic sql, change EXEC to PRINT if you want to see what will get executed added the quotename function because you need brackets around domain users

 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 3

Updating the answer from Yoopergeek, you could have

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