Login leri database lere yetkilendirirken Database Role lar kullanarak gruplarız.
Örneğin Developer lar için bir database i role ü oluşturur ve bu role e yetkilendirmeler yaparız. Developer User larıda bu role e ekleyerek otomatik yetkilendirmiş oluruz. Daha sonra developer yetkilerinde bir değişiklik yapmak istediğimizde tek tek developer user larda oynama yapmak yerine developer role ünü değiştiririz ve bütün developer user lar yansımasını sağlarız.
Böyle bir yapı kurgulamamız user ları daha iyi ve hızlı yönetmemizi sağlar.
Bugünkü konumuz ise bir DB için tanımlanmış Database Role de hangi user lar olduğunu listelemek.
Ortamlar arası DB taşıması yaparken, role lerde ki userlarıda bu makaledeki SP i sayesinde rahatça taşıyor olacağız.
İlk olarak listelemeyi yapacak olan SP ye bakalım.
USE [master]
GO
CREATE PROCEDURE [GetRoleMembers] @DBName sysname, @RoleName sysname
As
CREATE TABLE ##tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)
CREATE TABLE ##tmp_names (name sysname)
declare @str varchar(max)=''
set @str='
declare @generation int
set @generation = 0
INSERT INTO ##tmp_role_member_ids (id) SELECT
rl.principal_id AS [ID]
FROM
'+@DBName+'.sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name='''+@RoleName+''')
UPDATE ##tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@generation
WHILE ( 1=1 )
BEGIN
INSERT INTO ##tmp_role_member_ids (id, role_id, sub_role_id, generation)
SELECT a.member_principal_id, b.role_id, a.role_principal_id, @generation + 1
FROM '+@DBName+'.sys.database_role_members AS a INNER JOIN ##tmp_role_member_ids AS b
ON a.role_principal_id = b.id
WHERE b.generation = @generation
if @@ROWCOUNT <= 0
break
set @generation = @generation + 1
END
DELETE ##tmp_role_member_ids WHERE id in (SELECT
rl.principal_id AS [ID]
FROM
'+@DBName+'.sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name='''+@RoleName+''') )
UPDATE ##tmp_role_member_ids SET generation = 0;
INSERT INTO ##tmp_role_member_ids (id, role_id, generation)
SELECT distinct id, role_id, 1 FROM ##tmp_role_member_ids
DELETE ##tmp_role_member_ids WHERE generation = 0
insert ##tmp_names
SELECT
u.name AS [Name]
FROM
'+@DBName+'.sys.database_principals AS rl
INNER JOIN ##tmp_role_member_ids AS m ON m.role_id=rl.principal_id
INNER JOIN '+@DBName+'.sys.database_principals AS u ON u.principal_id = m.id
WHERE
(rl.type = ''R'')and(rl.name='''+@RoleName+''')
ORDER BY
[Name] ASC
'
exec (@str)
--print @str
select Name from ##tmp_names
drop table ##tmp_role_member_ids
drop table ##tmp_names
Gördüğünüz gibi SP yi Master DB sinde create ediyoruz. Her DB için ayrı SP kullanmaktansa DB yi parametre olarak göndereceğiz.
2. parametre ise role parametresi. Hangi DB içindeki hangi Role de ki user lar?
Kullanımına bakacak olursak;
Use Master
GO
exec GetRoleMembers 'AdventureWorks','My_Role'
GO
Şimdi örneğimize geçelim.
İlk önce tanımlamaları yapıyoruz.
User ları create edelim.
use Master
GO
CREATE LOGIN User1 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User2 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User3 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User4 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User5 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
GO
User ları AdventureWorks DB sinde açalım.
Use AdventureWorks
GO
CREATE User [User1] FOR LOGIN [User1];
CREATE User [User2] FOR LOGIN [User2];
CREATE User [User3] FOR LOGIN [User3];
CREATE User [User4] FOR LOGIN [User4];
CREATE User [User5] FOR LOGIN [User5];
GO
Yeni bir DB Role tanımlayalım.
Use AdventureWorks
GO
CREATE ROLE [My_Role] AUTHORIZATION [dbo]
GO
User ları bu Role e ekleyelim.
Use AdventureWorks
GO
EXEC sp_addrolemember N'My_Role', N'User1';
EXEC sp_addrolemember N'My_Role', N'User2';
EXEC sp_addrolemember N'My_Role', N'User3';
EXEC sp_addrolemember N'My_Role', N'User4';
EXEC sp_addrolemember N'My_Role', N'User5';
GO
Tanımlamalar bitti. Şimdi SP yi kullanarak AdventureWorks DB sindeki My_Role DB Role üne eklenmiş user ları listeleyelim.
Use Master
GO
exec GetRoleMembers 'AdventureWorks','My_Role'
GO
Name
============
User1
User2
User3
User4
User5
Bu SP yi kullanarak user lar için Role e ekleme script i de oluşturabilirsiniz. Bunuda size bırakıyorum J
İyi çalışmalar,
Turgay Sahtiyan
40b6f1b0-4868-46a7-ac69-599171b49fcd|0|.0