Give permissions to Create/Drop views only-CodePudding

I'm working with

Microsoft SQL Server 2019 (RTM-GDR) - 15.0.2095.3 (X64) Apr 29 2022 18:00:13   
Copyright (C) 2019 Microsoft Corporation  
Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

I want to give a user rights to only CREATE and DROP VIEWS and deny rights to CREATE and DROP tables, schemas, or the whole database.

Is there a combination on the permissions level available to achieve this? So far all I found was that ALTER rights on the schema are required to create views which also give users rights to actions I want them to deny.

The background is that we want to give users read-only rights to the database with the exception of creating and deleting views.

CodePudding user response：

As I mentioned in my (now deleted) comments, using a schema might be the easier solution here. Although you can give a USER permissions to explicitly CREATE a VIEW the ALTER can't be an granular. Instead, however, you could give them access to a specific schema and then they can create (and ALTER) their views as they see fit. This is a "quick" example demonstrating the method:

USE master;
GO
CREATE DATABASE TestDB;
GO

USE TestDB;
GO
CREATE TABLE dbo.YourTable (ID int IDENTITY(1,1) CONSTRAINT PK_YourTable PRIMARY KEY,
                            SomeInt int NOT NULL);
GO

CREATE TABLE dbo.AnotherTable (ID int IDENTITY  CONSTRAINT PK_AnotherTable PRIMARY KEY,
                               YourID int NOT NULL CONSTRAINT FK_AnotherTable_YourID FOREIGN KEY REFERENCES dbo.YourTable(ID),
                               SomeDate date NULL);
GO

INSERT INTO dbo.YourTable (SomeInt)
VALUES(1),(17),(12),(1634),(-5);
GO

INSERT INTO dbo.AnotherTable (YourID,
                              SomeDate)
VALUES(1,GETDATE()),(1,'20220101'),
      (3,GETDATE()),
      (4,'20221001'),(4,'20221002'),(4,'20221003'),
      (5,'20221215'),(5,'20221015');
GO

GO

CREATE SCHEMA V; --V for Vende.... View
GO

CREATE USER SomeUser WITHOUT LOGIN;
GO

GRANT ALTER, CONTROL,SELECT ON SCHEMA::V TO SomeUser;
GRANT CREATE VIEW TO SomeUser;
GO

EXECUTE AS USER = 'SomeUser';
GO
--This will fail
CREATE VIEW dbo.NoAccess AS

    SELECT ID,
           YourID,
           SomeDate
    FROM dbo.AnotherTable;
GO
SELECT *
FROM dbo.NoAccess;
GO
--This'll work
CREATE VIEW V.RelatedRows AS

    SELECT Y.ID AS YourID,
           Y.SomeInt,
           A.ID AS AnotherID,
           A.SomeDate
    FROM dbo.YourTable Y
         JOIN dbo.AnotherTable A ON Y.ID = A.YourID;
GO
SELECT *
FROM V.RelatedRows;
GO
--Alter the View to a LEFT JOIN
CREATE OR ALTER VIEW V.RelatedRows AS

    SELECT Y.ID AS YourID,
           Y.SomeInt,
           A.ID AS AnotherID,
           A.SomeDate
    FROM dbo.YourTable Y
         LEFT JOIN dbo.AnotherTable A ON Y.ID = A.YourID;
GO

--For this example, they also have no direct access to the tables; you may need to change this.
SELECT *
FROM dbo.YourTable;

GO
REVERT;

GO
USE master;
GO
DROP DATABASE TestDB;

The above will both CREATE and ALTER the VIEW V.RelatedRows, while failing to create the VIEW dbo.NoAccess. As noted as well, I don't give explicit access to dbo schema to the USER, they can only access the data through permission chaining. That may not be desired, but I wanted to demonstrate that the user doesn't actually even need direct access to the table to be able to be able to use it in this scenario.