在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 @usernameI 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 )更多推荐
发布评论