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 execute RESTORE
. If the
database exists, RESTORE
permissions default to members of the
sysadmin
and dbcreator
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.
GRANT
ed orDENY
ed. Without more specifics it will be hard for us to determine what the best approach would be. Thanks.