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

有關資料庫的討論, 都可以在這發表哦~~
回覆文章
頭像
tim
文章: 1379
註冊時間: 2008年 11月 26日, 00:49

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

文章 tim »

在資料庫中建立 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 ['+@name+'] TO ['+@loginame+']' 
      --print @cmd 
      exec sp_executesql @cmd 
      fetch next from cursor_sp into @name  
    end 
     
    close cursor_sp 
    deallocate cursor_sp 
多多留言, 整理文章, 把經驗累積下來.....
頭像
tim
文章: 1379
註冊時間: 2008年 11月 26日, 00:49

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

文章 tim »

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

http://www.vikramlakhotia.com/Granting_ ... _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/4841 ... -procedure
多多留言, 整理文章, 把經驗累積下來.....
arvin
文章: 21
註冊時間: 2009年 2月 16日, 15:00

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

文章 arvin »

若要排除已加權限的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 ['+@name+'] TO ['+@loginame+']' 
      print @cmd 
--      exec sp_executesql @cmd 
      fetch next from cursor_sp into @name  
    end 
     
    close cursor_sp 
    deallocate cursor_sp 
頭像
tim
文章: 1379
註冊時間: 2008年 11月 26日, 00:49

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

文章 tim »

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

只是重點在於他的 xtype='FN', 其他的部分都是一樣的!
多多留言, 整理文章, 把經驗累積下來.....
arvin
文章: 21
註冊時間: 2009年 2月 16日, 15:00

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

文章 arvin »

補充一下,由於 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 ['+@name+'] TO ['+@loginame+']' 
      print @cmd 
--      exec sp_executesql @cmd 
      fetch next from cursor_sp into @name  
    end 
     
    close cursor_sp 
    deallocate cursor_sp 
回覆文章