t-sql 创建用户并授予执行存储过程的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3374436/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
t-sql create user and grant execute on permission for stored procedures
提问by stackoverflowuser
I have a script which creates a database, stored procs, views, tables, udf. I want to include a script to create a user 'user_1' and give execute permission on the database.
我有一个创建数据库、存储过程、视图、表、udf 的脚本。我想包含一个脚本来创建用户“user_1”并授予对数据库的执行权限。
I tried following to create grant exec command for all stored procs
我尝试按照以下步骤为所有存储的过程创建 grant exec 命令
declare @permission varchar(max)
select @permission = COALESCE(
@permission + '; ' + 'Grant Execute on ' + name + ' user_1',
'Grant Execute on ' + name + ' user_1')
from sysobjects where xtype in ('P')
exec (@permission)
But exec (@permission)does not work. It gives
但exec (@permission)不起作用。它给
incorrect syntax near ';'.
';' 附近的语法不正确。
How can I solve this?
我该如何解决这个问题？
回答by Bill
Create Login: creates the server level login. Then... Create User: lets the Login account attach to your database. Then... Grant Execute To: grants execute rights to ALL of the sp's and functions in your db. Use "Grant Execute ON abc TO xyz" if you only want to grant rights to specific sps.
创建登录：创建服务器级登录。然后... 创建用户：让登录帐户附加到您的数据库。然后... Grant Execute To：授予对数据库中所有 sp 和函数的执行权限。如果您只想授予特定 sps 的权限，请使用“Grant Execute ON abc TO xyz”。
Create login abacadaba with password='ABVDe12341234';
Create user abacadaba for login abacadaba;
Grant Execute to abacadaba;
回答by del.ave
Have you tried:
你有没有尝试过：
CREATE LOGIN TestUser WITH PASSWORD = 'TopSecret'
GRANT EXEC ON MyStoredProc TO TestUser
you can also "CREATE USER" if that's what you want.
如果这是您想要的，您也可以“创建用户”。
回答by StephenS
Had exactly the same problem as original user, but for me it was bad characters embedded in the TSQL - I'm guessing from whatever source it was cut and pasted from.
与原始用户有完全相同的问题，但对我来说，它是嵌入在 TSQL 中的坏字符 - 我猜测它是从任何来源剪切和粘贴的。
Anyway depending on how much whitespace you have, just delete the whitespace between the words and replace with regular spaces.
无论如何，取决于您有多少空格，只需删除单词之间的空格并替换为常规空格即可。
Try all the other answers before this, it's fairly unlikely - I'm only adding it as it was so frustrating having 2 lines of TSQL that looked identicalabove/below each other, but resulted in different result messages when highlighted and run in Management Studio...
在此之前尝试所有其他答案，这是不太可能的 - 我只是添加它，因为它有 2 行 TSQL在彼此上方/下方看起来相同，但在 Management Studio 中突出显示并运行时导致不同的结果消息，这太令人沮丧了...
UPDATE: The bad characters were pasted from Microsoft Lync
更新：坏字符是从 Microsoft Lync 粘贴的
