As I don’t bother with security issues on a regulary base, it happens that I forget the necessary steps. Therefore I use this blog post as a documentation and personal knowledge base…
- Make sure that the schema your objects you want to grant access to, are within a schema that is owned by dbo. Otherwise you have to grant access to all objects used that are used by the respective object. Example: if a stored procedure queries a table than the access to the table would be necessary.
- Create Login if necessary
CREATE LOGIN [DOMAIN\UserGrp_MIS_XYZ] FROM WINDOWS;
- Create User in BL
CREATE USER [DOMAIN\UserGrp_MIS_XYZ] FROM LOGIN [DOMAIN\UserGrp_MIS_XYZ];
- Create Role and assign User/Group
CREATE ROLE ROLE_FOR_DOING_SOMETHING;
EXEC sp_addrolemember 'ROLE_FOR_DOING_SOMETHING', [DOMAIN\UserGrp_MIS_XYZ];
- Assign Secureables to Role – what should the role be allowed to do
GRANT SELECT ON View TO ROLE_FOR_DOING_SOMETHING;
GRANT EXECUTE ON USP_XYZ TO ROLE_FOR_DOING_SOMETHING;
Depending on the functionality of the object the Role grants access to, it may be necessary to create Users and Roles in other databases (like CORE or TOOL) as well. In that case repeat step 2 to 4.