在工作當中遇到一個類似這樣的問題：要對資料庫賬戶的權限進行清理、設定，其中有一個使用者test，隻能擁有資料庫myassistant的dml(更新、插入、删除等)操作權限，另外擁有執行資料庫存儲過程、函數的權限，但是不能進行ddl操作（包括建立、修改表、存儲過程等...），于是需要設定登入名test的相關權限：
1：右鍵單擊登入名test的屬性.
2: 在伺服器角色裡面選擇"public"伺服器角色。
3：在使用者映射選項當中，選擇"db_datareader"、"db_datawriter"、"public"三個資料庫角色成員。
此時，已經實作了擁有dml操作權限，如果需要擁有存儲過程和函數的執行權限，必須使用grant語句去授權，一個生産庫的存儲過程和函數加起來成千上百，如果手工執行的話，那将是一個辛苦的體力活，而我手頭有十幾個庫，是以必須用腳本去實作授權過程。下面是我寫的一個存儲過程，亮點主要在于會判斷存儲過程、函數是否已經授予了exe或select權限給某個使用者。這裡主要用到了安全目錄試圖sys.database_permissions，例如，資料庫裡面有個存儲過程dbo.sp_authorize_right，如果這個存儲過程授權給test使用者了話，那麼在目錄試圖sys.database_permissions裡面會有一條記錄,如下所示：
如果我将該存儲過程授予exec權限給test1，那麼
grant exec on dbo.sp_diskcapacity_cal to test;
grant exec on dbo.sp_diskcapacity_cal to test1;
select * from sys.sysusers where name ='test' or name ='test1'
其實grantee_principal_id代表向其授予權限的資料庫主體 id ，是以我就能通過上面兩個視圖來判斷存儲過程是否授予執行權限給使用者test與否，同理，對于函數也是如此，存儲過程如下所示，其實這個存儲過程還可以擴充，如果您有特殊的需要的話。
code snippet
use myassistant;
go
set ansi_nulls on;
set quoted_identifier on
if exists(select 1 from sysobjects where id=object_id(n'sp_authorize_right')
and objectproperty(id, 'isprocedure') =1)
drop procedure sp_authorize_right;
--====================================================================================================
-- procedurename : sp_authorize_right
-- author : kerry
-- createdate : 2013-05-10
-- blog : www.cnblogs.com/kerrycode/
-- description : 将資料庫的所有自定義存儲過程或自定義函數賦權給某個使用者(可以繼續擴充)
/*****************************************************************************************************
parameter : 參數說明
******************************************************************************************************
@type : 'p' 代表存儲過程 , 'f' 代表存儲過程，如果需要可以擴充其它對象
@user : 某個使用者賬戶
modified date modified user version modified reason
2013-05-13 kerry v01.00.01 排除系統存儲過程和系統函數的授權處理
2013-05-14 kerry v01.00.02 增加判斷,如果某個存儲過程已經賦予權限
則不做任何操作
*****************************************************************************************************/
create procedure sp_authorize_right
(
@type as char(10) ,
@user as varchar(20)
)
as
declare @sqltextvarchar(1000);
declare @userid int;
select @userid = uid from sys.sysusers where name=@user;
if @type = 'p'
begin
create table #procedurename( sqltext varchar(max));
insert into #procedurename
select 'grant execute on ' + p.name + ' to ' + @user + ';'
from sys.procedures p
where not exists( select 1
from sys.database_permissions r
where r.major_id = p.object_id
and r.grantee_principal_id = @userid
and r.permission_name is not null )
select * from #procedurename;
--select 'grant execute on ' + name + ' to ' +@user +';'
--from sys.procedures;
--select 'grant execute on ' + [name] + ' to ' +@user +';'
-- from sys.all_objects
--where [type]='p' or [type]='x' or [type]='pc'
declare cr_procedure cursor for
open cr_procedure;
fetch next from cr_procedure into @sqltext;
while @@fetch_status = 0
begin
execute(@sqltext);
fetch next from cr_procedure into @sqltext;
end
close cr_procedure;
deallocate cr_procedure;
end
else
if @type='f'
begin
create table #functionset( functionname varchar(1000));
insert into #functionset
select 'grant exec on ' + name + ' to ' + @user + ';'
from sys.all_objects s
from sys.database_permissions p
where p.major_id = s.object_id
and p.grantee_principal_id = @userid)
and schema_id = schema_id('dbo')
and( s.[type] = 'fn'
or s.[type] = 'af'
or s.[type] = 'fs'
or s.[type] = 'ft'
) ;
select * from #functionset;
--select 'grant exec on ' + name + ' to ' + @user +';' from sys.all_objects
-- where schema_id =schema_id('dbo')
-- and ([type]='fn' or [type] ='af' or [type]='fs' or [type]='ft' );
select 'grant select on ' + name + ' to ' + @user + ';'
and( s.[type] = 'tf'
or s.[type] = 'if'
) ;
select * from #functionset;
--select 'grant select on ' + name + ' to ' + @user +';' from sys.all_objects
-- where schema_id =schema_id('dbo')
-- and ([type]='tf' or [type]='if') ;
declare cr_function cursor for
select functionname from #functionset;
open cr_function;
fetch next from cr_function into @sqltext;
while @@fetch_status = 0
begin
print(@sqltext);
exec(@sqltext);
fetch next from cr_function into @sqltext;
end
close cr_function;
deallocate cr_function;
end