48 lines
2.6 KiB
Transact-SQL
48 lines
2.6 KiB
Transact-SQL
USE [site_fuchs]
|
|
GO
|
|
|
|
DECLARE @RoleName sysname
|
|
set @RoleName = N'fuchs_rwe'
|
|
|
|
IF @RoleName <> N'public' and (select is_fixed_role from sys.database_principals where name = @RoleName) = 0
|
|
BEGIN
|
|
DECLARE @RoleMemberName sysname
|
|
DECLARE Member_Cursor CURSOR FOR
|
|
select [name]
|
|
from sys.database_principals
|
|
where principal_id in (
|
|
select member_principal_id
|
|
from sys.database_role_members
|
|
where role_principal_id in (
|
|
select principal_id
|
|
FROM sys.database_principals where [name] = @RoleName AND type = 'R'))
|
|
|
|
OPEN Member_Cursor;
|
|
|
|
FETCH NEXT FROM Member_Cursor
|
|
into @RoleMemberName
|
|
|
|
DECLARE @SQL NVARCHAR(4000)
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
SET @SQL = 'ALTER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[')
|
|
EXEC(@SQL)
|
|
|
|
FETCH NEXT FROM Member_Cursor
|
|
into @RoleMemberName
|
|
END;
|
|
|
|
CLOSE Member_Cursor;
|
|
DEALLOCATE Member_Cursor;
|
|
END
|
|
/****** Object: DatabaseRole [fuchs_rwe] Script Date: 02.12.2020 21:05:05 ******/
|
|
DROP ROLE [fuchs_rwe]
|
|
GO
|
|
/****** Object: DatabaseRole [fuchs_rwe] Script Date: 02.12.2020 21:05:05 ******/
|
|
CREATE ROLE [fuchs_rwe]
|
|
GO
|
|
ALTER AUTHORIZATION ON ROLE::[fuchs_rwe] TO [dbo]
|
|
GO
|