what would be the right permission to allow everything else but overwriting or creating a database?
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 thesysadmin
SQL Server role andDENY
the right tobackup database
from the database principalhot2use
: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 thedb_owner
database role for theTest
database, we log in withhot2use
......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 roledb_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 beGRANT
ed back from the lesser privileged account, even thought the SQL Login (e.g.hot2use
) should have these privileges from the database roledb_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
To be able answer your question we need to know what exactly permissions at server level your developers need.
You are approaching from the wrong side: you gave them sysadmin
server level role and try to limit sysadmin
, but it's not possible (there were some changes in last versions, but they concern with DATA access, not administrative privileges, you can now hide your data from sysadmin, but not to limit its power in administrative tasks).
So instead of trying to DENY
something to sysadmin
(that is impossible) you should think what permissions should you GRANT
.
Your developers should not be members of sysadmin
, otherwise it's not possible to DENY something to them.
Make a list of what your developers should be able to do, and update your question. Maybe it can be achieved by creating custom server role
and by granting to it only necessary permissions, or maybe it can be done using stored procedures
signed with certificate
.
The most difficult thing to accomplish without sysadmin rights is this one:
They still should be able to create and run jobs
As soon as you remove your logins from sysadmin
role, SQL Server Agent
will disappear for them from SSMS.
To be able to use SQL Server Agent server principal should be at least a member of SQLAgentUserRole
, to be able to run jobs -- a member of SQLAgentOperatorRole
, you can read more on it here: SQL Server Agent Fixed Database Roles. But even SQLAgentOperatorRole
is still limited compared to sysadmin
, so read the article and decide if those permissions are sufficient for your devs, if no, SQL Server Agent
use will require a sysadmin
membership.
ABOUT RESTORE PERMISSIONS
If the database being restored does not exist, the user must have
CREATE DATABASE
permissions to be able to executeRESTORE
. If the database exists,RESTORE
permissions default to members of thesysadmin
anddbcreator
fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.
This is a cite from official MS documentation that you can read here: RESTORE Statements (Transact-SQL) under PERMISSIONS.
It clearly states that someone CANNOT restore/create database
without SERVER LEVEL PERMISSIONS
.
This means that to resolve your problem you don't need to deny anything at database level, it won't help you in restriction of RESTORE
ability.
Who has no server permissions
(membership in dbcreator
or sysadmin
server roles), already cannot restore/create database
.
Who has these permissions, cannot lose them if you deny
any database level permission.
Now, the other answerer has made a claim that I feel needs to be addressed:
“No backup privilege = No restore privilege”
Let's investigate it. Here is a simple repro.
As stated in BOL article, to be able to RESTORE a database one should be a member of dbcreator
or sysadmin
server roles.
So if one that is just db_owner
(and certainly HAS backup
permission) CANNOT restore database.
Let's create a new database, backup it, create a login that will be mapped to that database and added to db_owner database role, we'll using pictures that convince more then code..
use master;
GO
create database test;
GO
create login hot2use with password = '*****';
GO
use test;
create user hot2use from login hot2use;
exec sp_addrolemember 'db_owner', 'hot2use';
go
execute as login = 'hot2use';
select is_member('db_owner') as is_db_owner;
select HAS_PERMS_BY_NAME('test', 'database', 'backup database') as has_backup_database;
select HAS_PERMS_BY_NAME('test', 'database', 'backup log') as has_backup_log;
All 3 last queries return 1 as hot2use
is db_owner
, has backup database
and backup log
permissions.
Now we check if hot2use is sysadmin
or dbcreator
server role member (that are able to restore
):
select IS_SRVROLEMEMBER('sysadmin') as is_sysadmin;
select IS_SRVROLEMEMBER('dbcreator') as is_dbcreator;
This code returns 0 in both cases:
Now close all the connections to test
database and backup it:
backup database test to disk = 'Z:\TEMP\test_full.bak';
go
Now our hot2use tries to restore test
database:
restore database test from disk = 'Z:\TEMP\test_full.bak' with replace;
Wow.
Msg 3110, Level 14, State 1, Line 1 User does not have permission to RESTORE database 'test'.
How can it be that our hot2use
has both backup database
and backup log
permissions and is not able to restore
?
It's simple. Backup
permission is needed to backup
, to be able to restore
, re-read BOL article and see WHO has RESTORE
permission that is necessary to be able to RESTORE
:
So to be able to RESTORE one should be a member of sysadmin
or dbcreator
server level roles.
Let's fix it and put our login hot2use
in dbcreator
role:
exec sp_addsrvrolemember 'hot2use', 'dbcreator';
Now our hot2use
is able to restore
:
Finally, we now DENY
backup database
and backup log
permission to hot2use
and he will retry to restore
:
use test;
deny backup database to hot2use;
deny backup log to hot2use;
GO
execute as login = 'hot2use';
select is_member('db_owner') as is_db_owner;
select HAS_PERMS_BY_NAME('test', 'database', 'backup database') as has_backup_database;
select HAS_PERMS_BY_NAME('test', 'database', 'backup log') as has_backup_log;
select IS_SRVROLEMEMBER('sysadmin') as is_sysadmin;
select IS_SRVROLEMEMBER('dbcreator') as is_dbcreator;
revert;
So now hot2use
has NO backup database
, backup log
permission, but he is still member of dbcreator
server role.
Will he be able to restore?
Yes of course. This is because, as stated in the BOL and demonstrated above, no backup permission is needed to be able to RESTORE.