Sql-server – SQL Server 2014 Database vs. Schema Permissions

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.

First you have to

• Create Login with create database permission. Note that the login cannot drop any databases.

  USE [master]
  GO
  
  CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
  DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  GO
  use [master]
  GO
  -- Grant create any database, but not drop or alter any database
   GRANT CREATE ANY DATABASE TO [Kin]
  GO
  

• Map the login to the database that you want to have permission to alter or drop.

  -- for drop database, you just need db_owner
  USE [test_drop]
  GO
  CREATE USER [Kin] FOR LOGIN [Kin] 
   GO
  

• Add the user to the db_owner role.

    EXEC sp_addrolemember 'db_owner', 'Kin'
  

Now test it for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop


EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))


EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin] 
set online

Below is the error :

Msg 5011, Level 14, State 9, Line 2 User does not have permission to alter database 'test_kin', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed.