Using variables with GRANT and DENY in SQL Server 2005

sql-server, sql-server-2005

Solution

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 )

Problem

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)

Original source