So, I'm trying to fix logins/users permissions here ( everybody was using DBO for everything, weak passwords and etc ).
For each customer we have, I will create only one login. It will access test databases, homologation, and etc.
I will give the login these permissions:
USE [master]
GO
CREATE LOGIN [User_Customer]
WITH PASSWORD=N'Strong_Pass',
DEFAULT_DATABASE=[Customer_DB],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [Customer_DB]--BULK
GO
USE Customer_DB
GO
CREATE USER [User_Customer] FOR LOGIN [User_Customer] --User
GO
ALTER ROLE [db_datareader] ADD MEMBER [User_Customer] --DR
GO
ALTER ROLE [db_datawriter] ADD MEMBER [User_Customer] --DW
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [User_Customer] --DDL
GO
GRANT EXECUTE TO [User_Customer] --EXECUTE
GO
USE [master]
GO
DENY VIEW ANY database to [User_Customer] --DENY
Is there a way to create a single role, where I can only crate the role, and assign the users mapped to this login, to the role?
I don't want to sometime forget a permission and mess everything.
I appreciate any tips about how to manage logins and users better than what I'm currently doing.
(The above script is for sql 2014. It's just for you guys to get the Idea. with sql server 2008, it's a different way ( sp_addrolemember and etc ).