在SQL Server 2005中使用GRANT和DENY的变量(Using variables with GRANT and DENY in SQL Server 2005)

在SQL Server 2005中使用GRANT和DENY的变量(Using variables with GRANT and DENY in SQL Server 2005)

在SQL Server 2005中，如何使用用户名的变量将GRANT或DENY权限用于数据库对象？ 我努力了：

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

我Incorrect syntax near '@username'得到了Incorrect syntax near '@username' ，所以我把它包装在[和]

GRANT SELECT ON [mytable] TO [@username]

但是，这会导致Cannot find the user '@username', because it does not exist or you do not have permission 。 我怎样才能做到这一点，而不必输入每个语句的用户名？ 我想这样做是为了减少任何错字的机会（这可能会导致错误的用户获得权限设置）

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)

最满意答案

你需要动态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 )

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 )