SQL Server Permissions – Allow Everything Except Overwriting or Creating Database

developmentpermissionsrestoresql serversql-server-2016

I have a test environment where the AD group mycompany\developers is currently sysadmin.

Recently I have had some issues, specially regarding to people restoring databases. My concerns are:

sometimes the database is involved in replication
the permissions are overwritten – the correct way is to save the
current permissions before the restore and re-apply them after the restore.
Have you checked the current available disk space in all drivers,
specially those used in this restore?
there might be other people using or working on that specific
database, and restoring over it without communication can cause
someone's else work being lost
in the dev environment the databases can be in simple recovery mode.
Have you shrunk the log and changed the database to simple recovery
mode.
in this particular case, the full backup for every user database is
going to run automatically every night. but anyways, have you
checked for scheduled backups?
have you deleted from the server the backup that you used to do the
restore?
the folders and drives or data and logs are different in live and
test. are you sure you moved the files to the right places?
Have you had a look at orphaned users and logins?

I particularly have concerns when developers create new databases, even in the dev environment. why?

because they start by creating the database in dev and then just ask me to copy it to live.
have you done any capacity planning for this database? how big it should be and how much it would grow in a month?

have you isolated or considered different filegroups for demainding
objects\indexes?
Have you checked the current available disk space in all drivers,
specially those used in this DB creation?
how big is the transaction log and why? Initially I like to set it
1.5 times the biggest clustered index.
what are the autogrowth settings?
what are the current permissions?

I have realised some people are not so easy to deal with, I rather reduce their power over that server.

I don't want to do their jobs, but I would like to restrict them from creating new databases, or overwriting existing ones, either by restoring or by attaching files, etc.

They still should be able to create and run jobs, create\alter any object withing user databases, create logins, grant permissions etc…

Basically the question is:

How to limit the power of a current sysadmin?
I thought about not granting sysadmin but a set of server permissions (excluding those to create/alter databases) that would allow them to do everything else.

what would that set of server permissions be?

Best Answer

TL;DR

In order to restrict the DEVs from doing anything "wrong" you have to at least remove the sysadmin SQL Server role.

Let's start

You could assign your DEVs the db_owner database role for each individual database. However that role will permit a user to perform a BACKUP DATABASE ... or BACKUP LOG ... which you mentioned you don't want them to be able to perform.

Solutions

Beginning with SQL Server 2014 you can then restrict a SQL Server Login with the db_owner role further removing individual privileges/permissions. One example is to remove the backup privilege on the database and transaction log level by issuing the following commands on the database:

USE <database_name>
GO
DENY BACKUP DATABASE TO <database_principal>
GO
DENY BACKUP LOG TO <database_principal>

Reference: DENY Database Permissions (Transact-SQL) (Microsoft Docs)

There are three levels of permissions.

GRANT  : You are granted/granting a specific permission  
REVOKE : A previously granted/denied permission is revoked (removed) again  
DENY   : You are denied/denying a specific permission

Alternative

Now because DENY has precedence over GRANT you could use the alternative solution of creating a new database role and adding the <database_principals> to that role:

USE <database_name>
GO
CREATE ROLE [deny_backup]
GO
DENY BACKUP DATABASE TO [deny_backup]
GO
DENY BACKUP LOG TO [deny_backup]
GO
EXEC sp_addrolemember N'deny_backup', N'<database_principal>'
GO

Reference: Restrict Backup permission to user (Social MSDN)

Step by Step Guide

Because some people are sceptical about statements I have made in this post, I am adding the individual steps to show you how you can indeed DENY permissions from a SQL Login even though that login has the db_owner role.

Creating and Mapping SQL Login

Let's create a SQL Login and assign it the db_owner database role:
Deny Backup Database permissions

Now we switch to a higher privileged account like sa or a Windows Login (which would be you, the DBA asking this question) that has the sysadmin SQL Server role and DENY the right to backup database from the database principal hot2use:

In a perfect world you would also need to submit a DENY BACKUP LOG TO hot2use too.
Log in to server with hot2use and back up database

Seeing as hot2use has the db_owner database role for the Test database, we log in with hot2use...

...and try to back up the database:

Well that doesn't work and is what we expected. This is because the SQL Server login hot2use no longer has the privilege to back up the database even though the login still has the database role db_owner:
SQL Login wants to hack permissions

Seeing as the SQL Server login has the db_owner role and according to some people he can assign himself the permissions back, let's try that out:

(The test was conducted in a fresh database SQL Server Query window)
Summary

If you DENY permissions with a higher privileged account, then these privileges can not be GRANTed back from the lesser privileged account, even thought the SQL Login (e.g. hot2use) should have these privileges from the database role db_owner. This is something that changed in SQL Server 2014 (yes, the tests were conducted on a SQL Server 2014 instance).

Additional ressources

You can further restrict permissions according to the official overview which can be found here:

Permissions (Database Engine)

Further recommended reading:

Getting Started with Database Engine Permissions

Wait, restores...

Oh yes, we only denied the BACKUP DATABASE privilege didn't we? That has nothing to do with RESTORE DATABASE, or does it? Let's find out. I'll create a database backup with a higher privileged account/login:

Seems to work. Let's switch back to the hot2use SQL Server Query window and restore the backup:

As you can see, it isn't going to work, because the BACKUP DATABASE privilege also restricts the restore capability of that SQL Server login.

No backup privilege = No restore privilege.

How about `DENY BACKUP DATABASE FROM SYSADMIN`?

Well, since you asked. Let's try it out. I'll leave out the screen shots that show how I revoked the database role.

I assigned the SQL Login the sysadmin SQL Server role with:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [hot2use]
GO

...and then removed the permission to backup the database with the above mentioned DENY BACKUP DATABASE.. commands and then performed a database backup with:

backup database Test to disk = 'C:\temp\Test_Full_Backup_20171121.bak'

... which resulted in:

So you can't DENY permissions from a SQL Login with the sysadmin Server role, but you can DENY permissions if the SQL Login does not have the 'sysadmin' database role.

Minimal permissions required to backup / restore a database

If I assign the SQL Server login absolutely no SQL Server roles and assign only the db_backupoperator database role on a given database, then I can backup and restore the database. This is something that others believe the SQL login is unable to perform. The higher privileged account created the backup, so the lower privileged account (non-sysadmin) shouldn't be able to do a backup/restore.

After resetting all permissions of the SQL Server login hot2use to public on the server and db_backupoperator on the database level, I am presented with the following enumerated permissions:

According to others I shouldn't be able to perform a backup because I am missing a SQL Server role.

So I can in fact backup a database if I have the db_backupoperator role.

And again, if I DENY BACKUP DATABASE to the SQL Server login (on a SQL Server 2014 instance or greater), then I am no longer capable of backing up my database, even though I still have the db_backupoperator role on the database level:

If the SQL Login hot2use tries to REVOKE the previously denied permission then that will fail, with a similar message that I previously showed above.

Summary

Determing which permissions/privileges/roles you want to assign to your DEVs is going to be a tiring fight/issue, because they won't want to give up the sysadmin SQL Server role. You will have to find out which SQL Server Roles and which Database Roles they will exactly require to do their job. The worst-case is that everything will stay the same. The best case is that you will be able to restrict the DEVs in their permissions/privileges.

You will at least have to remove the sysadmin SQL Server role from your DEVs in order to further restrict them from being able to do everything they want. But once that SQL Server role has been removed, you can assign them for example the db_owner role and DENY BACKUP DATABASE ... to disallow them from backing up the database or restoring it.

The developers can still do anything they want inside the database (includig a DROP DATABASE), but they cannot perform a backup or a restore. See the following enumerated permissions for the SQL Server login hot2use with the database role db_owner but having both DENY BACKUP DATABASE ... and DENY BACKUP LOG ...:

SQL Server Login with db_owner role but without backup permissions

Related Solutions

Sql-server – Permission required to view a database diagram

From the documentation:

Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram's creator and any member of the db_owner role.

Ownership of diagrams can only be transferred to members of the db_owner role. This is only possible if the previous owner of the diagram has been removed from the database.

If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.

So it seems that you won't be able to do it with lower roles like db_datareader.

Behind the scenes, here is what Management Studio is calling to drive the list:

CREATE PROCEDURE dbo.sp_helpdiagrams
(
    @diagramname sysname = NULL,
    @owner_id int = NULL
)
WITH EXECUTE AS N'dbo'
AS
BEGIN
    DECLARE @user sysname
    DECLARE @dboLogin bit
    EXECUTE AS CALLER;
        SET @user = USER_NAME();
        SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
    REVERT;
    SELECT
        [Database] = DB_NAME(),
        [Name] = name,
        [ID] = diagram_id,
        [Owner] = USER_NAME(principal_id),
        [OwnerID] = principal_id
    FROM
        sysdiagrams
    WHERE
        (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
        (@diagramname IS NULL OR name = @diagramname) AND
        (@owner_id IS NULL OR principal_id = @owner_id)
    ORDER BY
        4, 5, 1
END

So you can see this matches the documentation.

Now a couple of workaround ideas:

In a Logon trigger, update the principal_id of all diagrams to be the current login. This means they will have access to all diagrams until the next person logs in. Not optimal.
Use a trigger on the sysdiagrams table itself (it's not really a system table), and whenever a diagram is created or updated, add / update a copy for each principal (with their user name appended). Not optimal either, and you could have people overwriting each other's diagrams all day long.

Here is an idea of the second workaround - all you really have to maintain here is a list of the database principals that you want to be able to access the diagrams (you'll also want to have something to clean up diagrams that have been deleted, and also some periodic maintenance that deletes diagrams for principals that have been deleted):

CREATE TRIGGER dbo.sysdiagrams_distribute
ON dbo.sysdiagrams
WITH EXECUTE AS N'dbo'
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @p TABLE(principal_id INT, name SYSNAME);

  INSERT @p SELECT principal_id, name
    FROM sys.database_principals
    -- change this list:
    WHERE name IN (N'test_blat_user', N'test_blat_user2', N'dbo');

  UPDATE d 
    SET [version] = i.version, definition = i.definition
  FROM inserted AS i
  CROSS JOIN @p AS p
  INNER JOIN dbo.sysdiagrams AS d
  ON d.name = i.name
  AND d.principal_id = p.principal_id;

  INSERT dbo.sysdiagrams(name, principal_id, version, definition)
    SELECT i.name, p.principal_id, i.version, i.definition
    FROM inserted AS i
    CROSS JOIN @p AS p
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.sysdiagrams WHERE name = i.name
      AND principal_id = p.principal_id
    );
END
GO

After creating a couple of diagrams, here is what an abridged version of Object Explorer looked like for these users:

enter image description here

Now, dbo will collect a whole bunch of copies of diagrams, which maybe isn't necessary, but you probably want those to be the "master" in most circumstances.

Sql-server – SQL Server 2008R2 Database refresh in isolated environment

Just restore them over top.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY;

You may have to use the MOVE option if the folder paths for data/log files don't match, e.g.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'D:\SQLData\file.mdf',
  MOVE 'log_file_name'  TO 'E:\SQLLogs\file.ldf';

You can get the file names (and anticipate their sizes, too) from:

RESTORE FILELISTONLY FROM DISK = '\\share\folder\file.bak';