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

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 2

Will using dynamic SQL work in this case?

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