http://www.turgaysahtiyan.com/post/SQL-Server-e28093-SP%28Stored-Procedure%29-lere-Topluca-Yetki-Verme.aspx adresindeki yazımda Stored Procedure lere topluca nasıl yetki verilebileceğini anlatmıştım. Şimdi ise geçenlerde yazdığım bir başka SP den bahsedeğim. Bu SP ile sadece SP lere değil server da ki tüm objelere (table,function,view vs) topluca yetki verebilirsiniz.
SP şu şekilde.
CREATE PROC GivePermissionForAllObjects
@UserName varchar(max) = '' --Yetki verilmek istenen user ya da role
,@Databases varchar(Max) = '' --Yetki verilmek istenen DB ya da DB ler, Boş bırakılırsa bütün DB ler
,@GrantDeny varchar(Max) = 'DENY' --GRANT, DENY, REVOKE
,@PermissionType varchar(Max) = 'EXECUTE' --Alter, Execute, View Definition vs.
,@ObjectType varchar(2)='' --SP,View,Function vs.
AS
if (isnull(@ObjectType,'')='') or (isnull(@ObjectType,'')='?') begin
print '
--Object Types
--FN SQL_SCALAR_FUNCTION
--C CHECK_CONSTRAINT
--UQ UNIQUE_CONSTRAINT
--SQ SERVICE_QUEUE
--U USER_TABLE
--D DEFAULT_CONSTRAINT
--PK PRIMARY_KEY_CONSTRAINT
--V VIEW
--S SYSTEM_TABLE
--IT INTERNAL_TABLE
--P SQL_STORED_PROCEDURE
--TF SQL_TABLE_VALUED_FUNCTION
--TR SQL_TRIGGER
--PC CLR_STORED_PROCEDURE
--
'
Return
end
SET NOCOUNT ON
--variables
declare @DatabasesTmp varchar(max)=@Databases
declare @DBName sysname,@ProcName sysname,@SchemaName sysname
declare @sql varchar(max)
Declare @Tbl_DB Table(DB varchar(Max))
Create Table ##Prodecures(DBName sysname, ProcName sysname, SchemaName sysname)
--
--Split Databases
if @DatabasesTmp<>'' begin
while @DatabasesTmp <> '' begin
if CHARINDEX(',',@DatabasesTmp)>0 begin
insert @Tbl_DB select SUBSTRING(@DatabasesTmp,1,CHARINDEX(',',@DatabasesTmp)-1)
set @DatabasesTmp = SUBSTRING(@DatabasesTmp,CHARINDEX(',',@DatabasesTmp)+1,LEN(@DatabasesTmp))
end else begin
insert @Tbl_DB select @DatabasesTmp
set @DatabasesTmp = ''
end
end
end else begin
insert @Tbl_DB select name from sys.databases where database_id>4
end
--
--code
declare CursorX cursor for
select DB from @Tbl_DB
open Cursorx
fetch from Cursorx into @DBName
while @@FETCH_STATUS=0
begin
set @sql = 'Use ' +@DBName + '; '+CHAR(10)+'
insert ##Prodecures
select '''+@DBName+''',name,SCHEMA_NAME(schema_id) from sys.objects where type='''+@ObjectType+''' '
exec(@sql)
fetch next from Cursorx into @DBName
end
close Cursorx
deallocate Cursorx
declare CursorY cursor for
select DBName,ProcName,SchemaName from ##Prodecures
open CursorY
fetch from CursorY into @DBName,@ProcName,@SchemaName
while @@FETCH_STATUS=0
begin
set @sql = 'use ['+ @DBName + ']; ' +
@GrantDeny+' '+@PermissionType+' ON ['+@SchemaName+'].['+@ProcName+'] TO ['+@UserName+'] AS [dbo]; '
exec(@sql)
fetch next from CursorY into @DBName, @ProcName, @SchemaName
end
close CursorY
deallocate CursorY
Drop Table ##Prodecures
SET NOCOUNT OFF
--code
Object Type için kullanılabilecek parametreler aşağıdaki gibidir.
| FN |
SQL_SCALAR_FUNCTION |
| C |
CHECK_CONSTRAINT |
| UQ |
UNIQUE_CONSTRAINT |
| SQ |
SERVICE_QUEUE |
| U |
USER_TABLE |
| D |
DEFAULT_CONSTRAINT |
| PK |
PRIMARY_KEY_CONSTRAINT |
| V |
VIEW |
| S |
SYSTEM_TABLE |
| IT |
INTERNAL_TABLE |
| P |
SQL_STORED_PROCEDURE |
| TF |
SQL_TABLE_VALUED_FUNCTION |
| TR |
SQL_TRIGGER |
| PC |
CLR_STORED_PROCEDURE |
Bir kaç örnek vermek gerekirse;
turgay user ına bütün AdventureWorks ve AdventureWorks2008 DB lerinde ki bütün table lar için SELECT yetkisi vermek.
exec GivePermissionForAllObjects 'turgay','AdventureWorks,AdventureWorks2008','GRANT','SELECT','U'
turgay user ıne bütün DB lerde ki Stored Procedure ler için EXECUTE yetkisi vermek.
exec GivePermissionForAllObjects 'turgay','','GRANT','EXECUTE','P'
turgay user ına bütün DB lerde verilmiş View SELECT hakkını geri almak. (REVOKE)
exec GivePermissionForAllObjects 'turgay','','REVOKE','SELECT','V'
turgay user ı bütün DB lerde Sistem Table larını Inherit alsa dahi SELECT çekemesin
exec GivePermissionForAllObjects 'turgay','','DENY','SELECT','S'
Bu şekilde istediğiniz DB de ki objelere topluca yetki verebilir yada geri alabilirsiniz.
İyi çalışmalar.
900e856f-7101-4a9b-940b-192b8896fa17|0|.0