问题描述

我有一个创建数据库、存储过程、视图、表、udf 的脚本.我想包含一个脚本来创建用户user_1"并授予对数据库的执行权限.

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.

我尝试按照以下步骤为所有存储的过程创建授权执行命令

I tried following to create grant exec command for all stored procs

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)

但是 exec (@permission) 不起作用.它给

But exec (@permission) does not work. It gives

';' 附近的语法不正确.

incorrect syntax near ';'.

我该如何解决这个问题?

How can I solve this?

推荐答案

创建登录:创建服务器级登录.然后...创建用户:让登录帐户附加到您的数据库.然后...授予执行至:授予对数据库中所有 sp 和函数的执行权限.如果您只想授予特定 sps 的权限，请使用Grant Execute ON abc TO xyz".

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.

Create login abacadaba with password='ABVDe12341234';
Create user abacadaba for login abacadaba;
Grant Execute to abacadaba;