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 however also gives users rights to actions I want to deny them.
The background is that we want to give users read-only rights to the database with the exception of creating and deleting views.
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.