We have struggled with security added to the databases at the individual level. Not only does this create a lot of tedious work to add the users but we are left with a number of orphans that have left the organization. To clean this up I have built role level security and add the AD groups to the specific role. Here are the specific roles and rights:
/*
Built by Todd Palecek to simplify security management
Create default roles for database access
1. Developer
2. Admin
3. Reader
4. Business Analyst
5. Other as needed
AD groups are used to assign to the roles and users are added to the AD groups
thereby always keeping security up-to-date based on employment status
*/
--DEVELOPER role DEVELOPMENT server, AD Group 000-AppName-Devel
use msdb --allow to see and modify jobs
go
EXEC sp_addrole 'Developer'
GO
EXEC sp_addrolemember 'db_datareader', 'Developer'
GO
EXECUTE sp_addrolemember 'SQLAgentOperatorRole', 'Developer' --allow to create agent Jobs
GO
--add users to role
use XXXX --database where development will take place
go
EXEC sp_addrole 'Developer'
GO
EXEC sp_addrolemember 'db_ddladmin', 'Developer' --create, drop, modify objects; primarily stored procs
GO
EXEC sp_addrolemember 'db_datareader', 'Developer'
GO
EXEC sp_addrolemember 'db_datawriter', 'Developer'
GO
GRANT EXECUTE TO [Developer] --allow to execute any stored procs
go
GRANT VIEW DEFINITION to [Developer] --view stored proc tsql
go
GRANT VIEW DATABASE STATE TO [Developer] --query dmvs
go
--add users to role
use master
go
create server role [Developer]
GO
GRANT VIEW SERVER STATE TO [Developer] --query dmvs
go
--DEVELOPER role PRODUCTION server, AD Group 000-AppName-Devel
use msdb
go
EXEC sp_addrole 'DeveloperProd'
GO
EXEC sp_addrolemember 'db_datareader', 'DeveloperProd'
GO
EXECUTE sp_addrolemember 'SQLAgentReaderRole', 'DeveloperProd'
GO
--add users to role
use XXXX
go
EXEC sp_addrole 'DeveloperProd'
GO
EXEC sp_addrolemember 'db_datareader', 'DeveloperProd'
GO
GRANT VIEW DEFINITION to [DeveloperProd]
go
--add users to role
--BUSINESS ANALYST role DEVELOPMENT server, AD Group 000-AppName-BusAnlyst
use msdb --allow to see and modify jobs
go
EXEC sp_addrole 'BusinessAnalyst'
GO
EXEC sp_addrolemember 'db_datareader', 'BusinessAnalyst'
GO
EXECUTE sp_addrolemember 'SQLAgentReaderRole', 'BusinessAnalyst' --allow to view agent Jobs
GO
--add users to role
use XXXX--database where development will take place
go
EXEC sp_addrole 'BusinessAnalyst'
GO
EXEC sp_addrolemember 'db_datareader', 'BusinessAnalyst'
GO
EXEC sp_addrolemember 'db_datawriter', 'BusinessAnalyst'
GO
GRANT EXECUTE TO [BusinessAnalyst] --allow to execute any stored procs
go
GRANT VIEW DEFINITION to [BusinessAnalyst] --view stored proc tsql
go
--add users to role
--BUSINESS ANALYST role PRODUCTION server, AD Group 000-AppName-BusAnlyst
use msdb
go
EXEC sp_addrole 'BusinessAnalystProd'
GO
EXEC sp_addrolemember 'db_datareader', 'BusinessAnalystProd'
GO
EXECUTE sp_addrolemember 'SQLAgentReaderRole', 'BusinessAnalystProd'
GO
--add users to role
use DemandPlanningStage
go
EXEC sp_addrole 'BusinessAnalystProd'
GO
EXEC sp_addrolemember 'db_datareader', 'BusinessAnalystProd'
GO
GRANT VIEW DEFINITION to [BusinessAnalystProd]
go
--add users to role
--READER role DEVELOPMENT server, AD Group 000-AppName-R
use XXXX --database where development will take place
go
EXEC sp_addrole 'Reader'
GO
EXEC sp_addrolemember 'db_datareader', 'Reader'
GO
--add users to role
--READER role PRODUCTION server, AD Group 000-AppName-R
use DemandPlanningStage --database where development will take place
go
EXEC sp_addrole 'Reader'
GO
EXEC sp_addrolemember 'db_datareader', 'Reader'
GO
--add users to role
--BUS_SYS_ADMIN role DEVELOPMENT server, AD Group 000-AppName-BusSysAdmin
use msdb --allow to see and modify jobs
go
EXEC sp_addrole 'Bus_Sys_Admin'
GO
EXEC sp_addrolemember 'db_datareader', 'Bus_Sys_Admin'
GO
EXECUTE sp_addrolemember 'SQLAgentOperatorRole', 'Bus_Sys_Admin' --allow to create agent Jobs
GO
--add users to role
use XXXX --database where development will take place
go
EXEC sp_addrole 'Bus_Sys_Admin'
GO
EXEC sp_addrolemember 'db_ddladmin', 'Bus_Sys_Admin' --create, drop, modify objects; primarily stored procs
GO
EXEC sp_addrolemember 'db_datareader', 'Bus_Sys_Admin'
GO
EXEC sp_addrolemember 'db_datawriter', 'Bus_Sys_Admin'
GO
GRANT EXECUTE TO [Bus_Sys_Admin] --allow to execute any stored procs
go
GRANT VIEW DEFINITION to [Bus_Sys_Admin] --view stored proc tsql
go
GRANT VIEW DATABASE STATE TO [Bus_Sys_Admin] --query dmvs
go
--add users to role
use master
go
create server role [Bus_Sys_Admin]
GO
GRANT VIEW SERVER STATE TO [Bus_Sys_Admin] --query dmvs
go
--BUS_SYS_ADMIN role PRODUCTION server, , AD Group 000-AppName-BusSysAdmin
use msdb
go
EXEC sp_addrole 'Bus_Sys_AdminProd'
GO
EXEC sp_addrolemember 'db_datareader', 'Bus_Sys_AdminProd'
GO
EXECUTE sp_addrolemember 'SQLAgentReaderRole', 'Bus_Sys_AdminProd'
GO
--add users to role
use DemandPlanningStage
go
EXEC sp_addrole 'Bus_Sys_AdminProd'
GO
EXEC sp_addrolemember 'db_datareader', 'Bus_Sys_AdminProd'
GO
GRANT VIEW DEFINITION to [Bus_Sys_AdminProd]
go
--add users to role
--OTHER role DEVELOPMENT server
--OTHER role PRODUCTION server
/*add users to role
CREATE USER [XXXX\AD Group] FOR LOGIN [XXXX\AD Group] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember 'Developer', '[XXXX\AD Group]'
GO
--change schema to dbo
ALTER USER [XXXX\AD Group] WITH DEFAULT_SCHEMA=[dbo];
*/
