[MSSQL] 給予User預存程序的Execute權限 (腳本方式)

2012-02-11

14892
0
SQL Server
2012-09-06

因為懶得在網站上線後，還要手動進DB一個一個預存程序加入Execute權限，所以寫了個腳本


/*須要修改的變數值*/
Declare @username varchar(255)
Set @username='Shadow' 
 

 /*判斷Temp Table是否存在 */
IF object_id('tempdb..#MyTempTable') IS NOT NULL 
 BEGIN 
    DROP TABLE #MyTempTable  
 END 


SELECT  'Grant execute on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) + ' TO ' + @username As sqlCmd
into #MyTempTable
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0	
and ROUTINE_TYPE='PROCEDURE'
and ROUTINE_NAME not Like 'sp_%'
 /*系統預存程序名稱都是sp_開頭，如果剛好有User自訂預存程序名稱為sp_開頭的話，該預存程序要自己手動加入Execute權限*/


/*一列一列地執行命令*/
Declare @targetSql varchar(255) /*要執行的一道SQL命令*/
Declare myCursor Cursor for  
        Select sqlCmd From #MyTempTable /*宣告游標*/

open myCursor
 Fetch Next From myCursor into @targetSql
 
 While(@@FETCH_STATUS=0)
  Begin
  
  execute(@targetSql) 
  Fetch Next From myCursor into @targetSql
  End
 

close myCursor
deallocate myCursor

使用方式：

1. 先進SSMS管理工具新增一個login(登入)

可以登入伺服器

新增使用者對應

2. @username改成剛剛新增的user名稱(test)


/*須要修改的變數值*/
Declare @username varchar(255)
Set @username='test' 
 
 /*判斷Temp Table是否存在*/
IF object_id('tempdb..#MyTempTable') IS NOT NULL 
 BEGIN 
    DROP TABLE #MyTempTable  
 END 


SELECT  'Grant execute on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) + ' TO ' + @username As sqlCmd
into #MyTempTable
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0    
and ROUTINE_TYPE='PROCEDURE'
and ROUTINE_NAME not Like 'sp_%'
 /*系統預存程序名稱都是sp_開頭，如果剛好有管理員自訂預存程序名稱為sp_開頭的話，該預存程序要自己手動加入Execute權限*/


Declare @targetSql varchar(255) /*要執行的一道SQL命令*/
Declare myCursor Cursor for  
        Select sqlCmd From #MyTempTable /*宣告游標*/

open myCursor
 Fetch Next From myCursor into @targetSql
 
 While(@@FETCH_STATUS=0)
  Begin
  
  execute(@targetSql) 
  Fetch Next From myCursor into @targetSql
  End
 
close myCursor
deallocate myCursor

3. 執行與測試

執行完上述的命令，即完成

參考資料：

SQL to Grant EXECUTE permission to all Procedures & Functions

請問該如何區分系統SP與自己寫的SP

[MSSQL] 判斷Temp Table是否存在

回首頁