Sql-server – SQL Server 2008R2 – How to prevent a role from creating or altering views within a database

permissionssql-server-2008-r2view

I have a situation on SQL Server 2008 R2 where I am trying to prevent users of an AD group from being able to create new views, or alter any existing views, within a particular database. This AD group is mapped to a role in the database, so I'm working on tweaking the permissions that role has.

From the documentation and Management Studio it's straightforward to deny the CREATE VIEW permission at the database level. I am also familiar with denying the alter command at the schema level, but that would prevent the altering of tables as well as views. I'm hoping to just deny the ALTER VIEW permission at the database or schema level.

Is this possible?

Best Answer

You could deny access to each individual view. The downside here is that any new views would allow alter unless you had a mechanism to deny permission such as a DDL trigger.

Alternatively, you can revoke the alter command at the schema or DB level (this may require removing a DB role) and then grant access individually to tables via some mechanism.

Another option would be to create a DDL trigger on all alter events. That trigger would check to see if the object being altered is a view via EVENTDATA and if it is make sure the logged in individual is someone with access either by a list of names or using sys.login_token to check for a domain group. If they're not then an error can be raised.

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

While you may have a lot of users, it would be unusual for them to require their own views. The views should be in one schema (possibly the one owning the tables) and the users should query them by either prefixing the schemaname (eg vwowner.view) or using the

ALTER SESSION SET_CURRENT_SCHEMA=vwowner

Roles are transient. You can do a SET ROLE NONE to turn them all off. You can have multiple sessions for the same account with different roles enabled. That isn't compatible with the way that Oracle handles objects (where they are either valid or they are not; they can't be valid for some sessions and not for others).

Sql-server – Database user permissions: GRANT VIEW DEFINITION but DENY on some object types

You can create a role and then grant / revoke permissions to it. Any user that is a part of the role will inherit the permissions.

Below is an example to get you started :

 -- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the databas
GRANT 
      SELECT 

ON SCHEMA::dbo
      TO TableSelector 
GO

-- Add an existing user to the new role created 
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO

-- Revoke access rights on a schema from a role 
DENY ALTER  -- you can customize here ...
ON SCHEMA::dbo
      TO TableSelector

Best Answer

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

Sql-server – Database user permissions: GRANT VIEW DEFINITION but DENY on some object types

Related Question