[MSSQL]如何能將資料庫中的所有stored procedure給予某user執行權限

有關資料庫的討論, 都可以在這發表哦~~

[MSSQL]如何能將資料庫中的所有stored procedure給予某user執行權限

文章tim » 2008年 11月 28日, 17:25

在資料庫中建立 stored procedure 來存取資料庫很方便, 可以很容易的管理及管制存取的邏輯及行為, 但面對大量的資料庫中的 stored procedure, 預設是只有 dbo 有執行權, 但若是要給一般 user, 如 datareader 及 datawriter 這種角色時, 就比較麻煩去給該 user 大量的 stored procedure 執行權限了.

我們可以利用 sysobjects 系統表, 利用 xtype='P' 來取出所有的 stored procedure 名稱, 再利用 GRANT 指令給予權限, 語法如下,

GRANT EXECUTE ON [dbo].[sp_xxxx] TO [user_loginame]

如此只要利用一個 cursor 配合 sp_executesql 就能快速地給予 user 執行權限囉, 程式如下,

代碼: 選擇全部
     
    declare @name nvarchar(255), @cmd nvarchar(1000), @loginame nvarchar(200)
     
    select @loginame = 'user_loginame'
     
    declare cursor_sp cursor for
      select name from sysobjects where xtype='P'
     
    open cursor_sp
     
    fetch next from cursor_sp into @name
    while @@fetch_status=0
    begin
      select @cmd = 'GRANT EXECUTE on ['[email protected]+'] TO ['[email protected]+']'
      --print @cmd
      exec sp_executesql @cmd
      fetch next from cursor_sp into @name 
    end
     
    close cursor_sp
    deallocate cursor_sp
多多留言, 整理文章, 把經驗累積下來.....
頭像
tim
 
文章: 1286
註冊時間: 2008年 11月 26日, 00:49

Re: [MSSQL]如何能將資料庫中的所有stored procedure給予某user執行權限

文章tim » 2011年 1月 5日, 18:50

這篇文章是利用產生指令的手法來做:

http://www.vikramlakhotia.com/Granting_Execute_permission_on_All_Stored_Procedure_for_a_new_SQL_Server_User.aspx

代碼: 選擇全部
select 'Grant Execute on ' + name +  ' SQLServerUSERName'
from sysobjects where xtype in ('P')

select 'Grant select,insert,update,delete on ' + name + ' SQLServerUSERName''
from sysobjects where xtype in ('U','V')


這篇文章則是檢查是否已經有執行的權限了,
http://stackoverflow.com/questions/484145/ms-sql-server-check-to-see-if-a-user-can-execute-a-stored-procedure
多多留言, 整理文章, 把經驗累積下來.....
頭像
tim
 
文章: 1286
註冊時間: 2008年 11月 26日, 00:49

Re: [MSSQL]如何能將資料庫中的所有stored procedure給予某user執行權限

文章arvin » 2011年 1月 5日, 18:51

若要排除已加權限的sp 可修改如下:
代碼: 選擇全部
     
    declare @name nvarchar(255), @cmd nvarchar(1000), @loginame nvarchar(200)
     
    select @loginame = 'login_name'

    declare cursor_sp cursor for
   select name from sysobjects where xtype='P'
   and id not in (SELECT so.id FROM sys.database_permissions AS dp
    JOIN sys.sysobjects AS so    ON dp.major_id = so.id
   JOIN sys.database_principals dps ON dp.grantee_principal_id = dps.principal_id
    WHERE  so.xtype='P' and dp.type = 'ex' and dps.name = @loginame)
    open cursor_sp
     
    fetch next from cursor_sp into @name
    while @@fetch_status=0
    begin
      select @cmd = 'GRANT EXECUTE on ['[email protected]+'] TO ['[email protected]+']'
      print @cmd
--      exec sp_executesql @cmd
      fetch next from cursor_sp into @name 
    end
     
    close cursor_sp
    deallocate cursor_sp
arvin
 
文章: 20
註冊時間: 2009年 2月 16日, 15:00

Re: [MSSQL]如何能將資料庫中的所有stored procedure給予某user執行權限

文章tim » 2012年 3月 1日, 12:00

另外若是 function 的話, 也是一樣的原理.

只是重點在於他的 xtype='FN', 其他的部分都是一樣的!
多多留言, 整理文章, 把經驗累積下來.....
頭像
tim
 
文章: 1286
註冊時間: 2008年 11月 26日, 00:49

Re: [MSSQL]如何能將資料庫中的所有stored procedure給予某user執行權限

文章arvin » 2014年 7月 18日, 15:32

補充一下,由於 2008 之後的版本有Diagrams,若有不小心點到時會自動處產相關的sp, fu, CLR and table,
若要過濾這些資料,可以改成以下的code。
代碼: 選擇全部
    declare @name nvarchar(255), @cmd nvarchar(1000), @loginame nvarchar(200)
     
    select @loginame = 'exec_role'

    declare cursor_sp cursor for
   select name from sysobjects where xtype in ('P', 'FN', 'FS', 'FT')
   and id not in (SELECT so.id FROM sys.database_permissions AS dp
    JOIN sys.sysobjects AS so    ON dp.major_id = so.id
   JOIN sys.database_principals dps ON dp.grantee_principal_id = dps.principal_id
    WHERE  so.xtype in ('P', 'FN', 'FS', 'FT')  and dp.type = 'ex' and dps.name = @loginame)
   and id not in (select major_id from sys.extended_properties where ISNULL(name, '') = 'microsoft_database_tools_support')
    open cursor_sp
     
    fetch next from cursor_sp into @name
    while @@fetch_status=0
    begin
      select @cmd = 'GRANT EXECUTE on ['[email protected]+'] TO ['[email protected]+']'
      print @cmd
--      exec sp_executesql @cmd
      fetch next from cursor_sp into @name 
    end
     
    close cursor_sp
    deallocate cursor_sp
arvin
 
文章: 20
註冊時間: 2009年 2月 16日, 15:00


回到 資料庫專區(SQL/ORACLE/MySQL/...)

誰在線上

正在瀏覽這個版面的使用者:沒有註冊會員 和 2 位訪客

cron