問題描述

我有一個(gè)創(chuàng)建數(shù)據(jù)庫、存儲(chǔ)過程、視圖、表、udf 的腳本.我想包含一個(gè)腳本來創(chuàng)建用戶user_1"并授予對(duì)數(shù)據(jù)庫的執(zhí)行權(quán)限.

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.

我嘗試按照以下步驟為所有存儲(chǔ)的過程創(chuàng)建授權(quán)執(zhí)行命令

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 ';'.

我該如何解決這個(gè)問題?

How can I solve this?

推薦答案

創(chuàng)建登錄:創(chuàng)建服務(wù)器級(jí)登錄.然后...創(chuàng)建用戶:讓登錄帳戶附加到您的數(shù)據(jù)庫.然后...授予執(zhí)行至:授予對(duì)數(shù)據(jù)庫中所有 sp 和函數(shù)的執(zhí)行權(quán)限.如果您只想授予特定 sps 的權(quán)限，請(qǐng)使用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;

這篇關(guān)于t-sql 創(chuàng)建用戶并授予執(zhí)行存儲(chǔ)過程的權(quán)限的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！