Allow or restrict access to database objects

As part of a data warehouse project that I’m working on, I need to be able to grant or deny access to users. I do this by setting permissions at different levels on the SQL Server (database, schema & object).

I normally don’t have control over what analysis or visualisation apps are used to access the data warehouse. In rare cases, databases that users shouldn’t have access to (e.g. staging) are visible when they connect via their preferred app & this can cause confusion even if they can’t access any of the objects in the database. To remove visibility of the database, run the following.

USE [database_name]
GO
-- First, create a user in the database:
CREATE USER [user_name] FOR LOGIN [login_name];
GO
-- Block visibility of the database:
DENY VIEW DEFINITION TO [role_or_group_or_user];
GO
DENY CONNECT TO [role_or_group_or_user];

Note: CONNECT & VIEW ANY DATABASE permissions are allowed to logins via the public database role.

Within the data warehouse, there is usually a requirement to control what tables or views a user or a group of users can access (e.g. sensitive data should only be accessible to project teams that need them, not to everyone). To grant or deny access to a group of objects, it can be useful to assign them to their own schema & set permissions for the entire schema.

USE [database_name]
GO
-- If necessary, create a user in the data warehouse:
CREATE USER [user_name] FOR LOGIN [login_name];
GO
-- Allow or block access to an entire schema:
GRANT SELECT ON SCHEMA :: [schema_name] TO [role_or_group_or_user];
REVOKE SELECT ON SCHEMA :: [schema_name] TO [role_or_group_or_user];

Further to this, if there is a requirement to control access to a specific object (e.g. a view), permissions can be set for that object.

USE [database_name]
GO
-- Allow or block access to a view:
GRANT SELECT ON [schema_name].[view_name] TO [role_or_group_or_user];
REVOKE SELECT ON [schema_name].[view_name] TO [role_or_group_or_user];

For schemas & objects, we use REVOKE rather than DENY. This covers scenarios where users are members of groups with access to restricted data as well as a “general access” group that gives all members access to unrestricted data. By using REVOKE instead of DENY, we don’t accidentally block those users’ access to the restricted datasets that they need.