
-- Create a SQL login
CREATE LOGIN fred WITH 
 PASSWORD = 'StrongPW1' MUST_CHANGE,
 DEFAULT_DATABASE = AdventureWorks2012,
 CHECK_EXPIRATION = ON,
 CHECK_POLICY = ON;
GO

-- Windows login based on USER
CREATE LOGIN [SQL2K12-IE2\Mary] FROM WINDOWS;
GO

-- Windows login based on group
CREATE LOGIN [SQL2K12-IE2\payroll] FROM WINDOWS;
GO

USE AdventureWorks2008R2;
GO

-- Create users in the AdventureWorks2008R2 database
CREATE USER simon FROM LOGIN fred;
GO
CREATE USER Mary FROM LOGIN [SQL2K12-IE2\Mary];
GO
CREATE USER [SQL2K12-IE2\payroll] FROM LOGIN [SQL2K12-IE2\payroll];
GO

-- Create a new database role
CREATE ROLE payroll;
GO 

-- Add a user to the role using legacy sp_addrolemember
EXECUTE sp_addrolemember 'payroll', 'SQL2K12-IE2\payroll';
GO
-- Remove a user from the role using legacy sp_droprolemember
EXECUTE sp_droprolemember 'payroll', 'SQL2K12-IE2\payroll';
GO

-- Add a user to the role using ALTER ROLE
ALTER ROLE [payroll] ADD MEMBER [simon]
GO
-- Drop a user from the role using ALTER ROLE
ALTER ROLE [payroll] DROP MEMBER [simon]
GO


-- Cleanup objects from the database and server
DROP ROLE payroll;
GO
DROP USER simon;
GO
DROP USER Mary;
GO
DROP USER [SQL2K12-IE2\payroll];
GO
DROP LOGIN [SQL2K12-IE2\payroll];
GO
DROP LOGIN [SQL2K12-IE2\Mary];
GO
DROP LOGIN fred;
GO
