USE [Master]
GO
CREATE DATABASE [TestPerm];
GO
CREATE LOGIN testPerm1 WITH PASSWORD = 'testPerm1###',DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
CREATE LOGIN testPerm2 WITH PASSWORD = 'testPerm2###',DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
USE  [TestPerm]
GO
CREATE USER testPerm1;
GO
CREATE USER testPerm2;
GO
ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm1;
GO
GRANT ALTER ANY ROLE  TO testPerm1;
GO

USE  [TestPerm]
GO
EXECUTE AS USER='testPerm1'
SELECT SUSER_NAME(), USER_NAME();
ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm2;
REVERT;

USE [testPerm]
 GO
 ALTER ROLE [db_securityadmin] ADD MEMBER testPerm1;
 GO
 EXECUTE AS USER='testPerm1'
 SELECT SUSER_NAME(), USER_NAME();
 ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm2;
 REVERT;

USE [testPerm]
GO
ALTER ROLE [db_owner] ADD MEMBER testPerm1;
GO

USE [testPerm]
 GO
 EXECUTE AS USER='testPerm1'
 SELECT SUSER_NAME(), USER_NAME();
 ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm2;
 REVERT;

USE [TestPerm]
GO
CREATE ROLE [testPermission]
GO
ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm1;
GO

USE [TestPerm]
GO
ALTER ROLE [db_owner] DROP MEMBER testPerm1;
GO
ALTER ROLE [db_securityadmin] DROP MEMBER testPerm1;
GO

EXECUTE AS USER='testPerm1'
SELECT SUSER_NAME(), USER_NAME();
ALTER ROLE [testPermission] ADD MEMBER testPerm2;
REVERT;

USE [TestPerm]
GO
REVOKE ALTER ANY ROLE  TO testPerm1;
GO

EXECUTE AS USER='testPerm1'
SELECT SUSER_NAME(), USER_NAME();
ALTER ROLE [testPermission] DROP MEMBER testPerm2;
REVERT;

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'TestPerm'
GO
USE [master]
GO
ALTER DATABASE [TestPerm] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [TestPerm]
GO
DROP LOGIN [testPerm1]
GO
DROP LOGIN [testPerm2]
GO