Sql-server – Best approach instead of granting permissions all the time to logins

sql serversql server 2014sql-server-2008-r2

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

Best Answer

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

Related Solutions

Sql-server – User in multiple windows groups mapped to sql logins

If I am understanding your question correctly, then yes the user will be able to run both applications with database connection issues.

If User1 is part of Windows Groups Group1 and Group2, and Group1 is mapped as a SQL Server login, as well as Group2 is mapped to a different SQL Server login. ~~And if Group1 is mapped to a database user in Database1 and Group2 is mapped as a database user in Database2, then User1 should have access to both Database1 and Database2.~~ And if Group1 and Group2 are both mapped to TheOnlyDatabase, then the user will have the accumulations of both database users' permissions.

This will return only one of the database users (as expected):

select user_name() as database_user_name

This will return 1 for both queries if the users is a member of both Windows Groups:

select is_member('Domain\Group1') as member_of_group_1

select is_member('Domain\Group2') as member_of_group_2

Sql-server – User can alter procedures without permission

Do you see anything that has been granted or role membership that shouldn't exist?

EXECUTE AS USER = N'your user';
GO
SELECT * FROM sys.fn_my_permissions(NULL, NULL);
GO
REVERT;
GO

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(N'your user');
GO

SELECT r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your user';

SELECT r.name
FROM sys.server_role_members AS m
INNER JOIN sys.server_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.server_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your login';

It is possible these developers are in the db_owner role without your knowledge or have been granted ALL on the dbo or another schema.

Make sure you execute these statements in the right database, and also validate that your users are logging in as who they say they are logging in as. Perhaps they have the sa password and you don't know it. Finally, you should make sure that the database principal maps to the correct server-level login.

SELECT dp.name, sp.name
FROM sys.database_principals AS dp
FULL OUTER JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE 
(
  dp.type <> 'R' 
  OR sp.type NOT IN ('C', 'K')
)
ORDER BY 
  dp.name COLLATE DATABASE_DEFAULT + sp.name COLLATE DATABASE_DEFAULT DESC;

Best Answer

Related Solutions

Sql-server – User in multiple windows groups mapped to sql logins

Sql-server – User can alter procedures without permission

Related Question