CODEWITHSUNDEEP
sql-serversql-server-2005
SamWM
SamWM

Reputation: 5356

Using variables with GRANT and DENY in SQL Server 2005

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)

Upvotes: 5

Views: 6330

Answers (3)

Paulo Santos
Paulo Santos

Reputation: 11587

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

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135121

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 )

Upvotes: 9

Yoopergeek
Yoopergeek

Reputation: 5642

Will using dynamic SQL work in this case? 

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

Upvotes: 1

Related Questions