問題描述
我有一個創建數據庫、存儲過程、視圖、表、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;
這篇關于t-sql 創建用戶并授予執行存儲過程的權限的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！