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 ).