Best approach instead of granting permissions all the time to logins

Question

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

Answer 1

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?

Yes you can do exactly as you said, create a role, add it as a member to the roles you are interested in and grant it permissions the same way you did it with a single user:

create role MyCustomRole;

ALTER ROLE [db_datareader] ADD MEMBER MyCustomRole--[User_Customer] --DR
GO
ALTER ROLE [db_datawriter] ADD MEMBER MyCustomRole--[User_Customer] --DW
GO
ALTER ROLE [db_ddladmin]   ADD MEMBER MyCustomRole--[User_Customer] --DDL
GO
GRANT EXECUTE TO MyCustomRole--[User_Customer] --EXECUTE
GO

Now you can add your users to this role:

alter role MyCustomRole add member [User_Customer]; 

To control what permissions your user has now just impersonate it:

execute as user = 'User_Customer';

select *
from sys.fn_my_permissions(null, 'database');

revert;

---

One tip for SQL Server 2014:

I saw you want to deny view any database to your logins.

After such a deny your logins will not see databases in Object Explorer, I mean all the databases included those where they are mapped to.

I think it will render their work more complicated. I don't know what exactly reasons led you to this decision, but if you really need it maybe Contained databases appeared in 2012 is the solution for you.

When you make a database contained, it's database itself that authenticates your users. This way you do not need logins anymore, every user can be created in its own database directly and will "see" only that database in Object Explorer.

More on contained databases here: SQL Server 2012 Contained Database Feature

Answer 2

If you have a particular naming convention for these customer logins, you could create a server-scoped DDL Trigger on the CREATE_LOGIN event. I mention a naming convention because you will need to filter out logins you want to add permissons to so that you don't apply this logic to all CREATE LOGIN statements.

In this trigger, you should be able to apply all of these role memberships and grants, both the server-level ones and the database-level ones. You won't need a new database-level role for the five database-level permissions because there is no chance of forgetting one of them (it's in the code). You will probably need to use Dynamic SQL to achieve this, but there is no user input so I don't see any harm in it.