deny alter on schema::a to super_role
deny select, insert, delete, update on schema::a to super_role
deny alter on role::super_role to super_role
deny alter any user to super_role
deny alter any database ddl trigger to super_role

use master;
create database DBTestSecurity;
go

use DBTestSecurity;
go

create schema a;
go

create table a.Test (id int);
go

create user TestUser without login;
go

exec sp_addrolemember 'db_owner', 'TestUser';
go

create role super_role;
go

deny alter on schema::a to super_role
deny select, insert, delete, update on schema::a to super_role
deny alter on role::super_role to super_role
deny alter any user to super_role
deny alter any database ddl trigger to super_role
go

execute as user = 'TestUser';
go
select * from a.Test; --success
go
revert;
go

exec sp_addrolemember 'super_role', 'TestUser';
go

execute as user = 'TestUser';
go
select * from a.Test; --fail
go
revert;
go

execute as user = 'TestUser';
go
exec sp_droprolemember 'super_role', 'TestUser';
go
select * from a.Test; --success
go
revert;
go

use master;
alter database DBTestSecurity set single_user with rollback immediate;
drop database DBTestSecurity;
go

deny alter on role::super_role to super_role

deny alter any role to super_role

execute as user = 'TestUser';
go
select *
from sys.fn_my_permissions(null, 'database')

deny control to super_role

execute as user = 'TestUser';

use master;
go
create database [RT];
go
use RT;
go
create user Test without login with default_schema = dbo;
go
alter role db_owner add member Test;
--grant control on database::RT to Test;
go
create role rst authorization dbo;
go
deny alter to rst;
go
alter role rst add member Test;
go

execute as user = 'Test';
go
select * from sys.fn_my_permissions(null, 'database') where permission_name = 'ALTER ANY ROLE';
go
alter role rst drop member Test;
go
select * from sys.fn_my_permissions(null, 'database') where permission_name = 'ALTER ANY ROLE';
go
revert;
go

use master;
go
drop database RT;
go

    -- CHECK PERMISSIONS --
	-- Only member of db_owner can drop members from db-fixed roles --
	if (not is_member('db_owner') = 1) and
       (not ( (@roluid >=16384 and @roluid <  16400) and is_member('db_owner') = 1))         and
       (not ( (@roluid < 16384 or  @roluid >= 16400) and is_member('db_securityadmin') = 1)) and
       (not ( (@roluid < 16384 or  @roluid >= 16400) and 
			((is_member(user_name(@owner)) = 1) or (has_perms_by_name(@rolename, 'role', 'alter') = 1))
			))
    begin
		ROLLBACK TRANSACTION
		EXEC %%System().AuditEvent(ID = 1296321618, Success = 0, TargetLoginName = NULL, TargetUserName = @membername, Role = @rolename, Object = NULL, Provider = NULL, Server = NULL)
		raiserror(15247,-1,-1)
		return (1)
	end