2 added 13 characters in body
source | link

One thing to note is that RESTORE is a command, not really a permission that can be granted. Which is why your GRANT failed.

In the SQL Server Books Online at https://msdn.microsoft.com/en-us/library/ms186858.aspx has pretty much said the same thing from SQL Server 2000 until now, namely:

RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database ... members of the db_owner fixed database role do not have RESTORE permissions.

If the dbo, not db_owner, seems confusing it is like this. One login owns the database, it may be sa or MyDomain\MyLogin. That login maps to the dbo user and should have rights to restore the database. Inside the database, many users may be in the db_owner role, but since those are inside the database to be restored, they do not get the RESTORE permission.

So, you can make logins members of the dbcreator fixed server role or you can make one user the owner of a database and he should then be able to do a restore of that database.

To test the consequences I created a login TestLogin that is not a sysadmin for these tests.

TEST 1: Granted TestLogin the dbcreator server role and the public role in MyDatabase. (It is necessary that TestLogin have some rights inside the database to be restored.)

RESTORE DATABASE MyDatabase ... was successful.

NOTE: If the restored database did not already have TestLogin as a user, TestLogin lost access to the database once the restore was complete since the sysusers data was restored with the contents from the backup. So, TestLogin needs to exist in the source backup if this user is to retain rights some internal rights after the restore.

TEST 2: Revoked TestLogin from the dbcreator server role and the publicrole in MyDatabase. Made TestLogin the owner of the database.

USE MyDatabase
exec sp_changedbowner 'TestLogin'

RESTORE DATABASE MyDatabase ... was successful.

In other words, dbcreator can create NEW databases, but can only restore a database where it is already a user and still retain rights to that database. Hope that this helps you decide what to do.

One thing to note is that RESTORE is a command, not really a permission that can be granted. Which is why your GRANT failed.

In the SQL Server Books Online at https://msdn.microsoft.com/en-us/library/ms186858.aspx has pretty much said the same thing from SQL Server 2000 until now, namely:

RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database ... members of the db_owner fixed database role do not have RESTORE permissions.

If the dbo, not db_owner, seems confusing it is like this. One login owns the database, it may be sa or MyDomain\MyLogin. That login maps to the dbo user and should have rights to restore the database. Inside the database, many users may be in the db_owner role, but since those are inside the database to be restored, they do not get the RESTORE permission.

So, you can make logins members of the dbcreator fixed server role or you can make one user the owner of a database and he should then be able to do a restore of that database.

To test the consequences I created a login TestLogin that is not a sysadmin for these tests.

TEST 1: Granted TestLogin the dbcreator server role and the public role in MyDatabase. (It is necessary that TestLogin have some rights inside the database to be restored.)

RESTORE DATABASE MyDatabase ... was successful.

NOTE: If the restored database did not already have TestLogin as a user, TestLogin lost access to the database once the restore was complete since the sysusers data was restored with the contents from the backup. So, TestLogin needs to exist in the source backup if this user is to retain rights after the restore.

TEST 2: Revoked TestLogin from the dbcreator server role and the publicrole in MyDatabase. Made TestLogin the owner of the database.

USE MyDatabase
exec sp_changedbowner 'TestLogin'

RESTORE DATABASE MyDatabase ... was successful.

In other words, dbcreator can create NEW databases, but can only restore a database where it is already a user and still retain rights to that database. Hope that this helps you decide what to do.

One thing to note is that RESTORE is a command, not really a permission that can be granted. Which is why your GRANT failed.

In the SQL Server Books Online at https://msdn.microsoft.com/en-us/library/ms186858.aspx has pretty much said the same thing from SQL Server 2000 until now, namely:

RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database ... members of the db_owner fixed database role do not have RESTORE permissions.

If the dbo, not db_owner, seems confusing it is like this. One login owns the database, it may be sa or MyDomain\MyLogin. That login maps to the dbo user and should have rights to restore the database. Inside the database, many users may be in the db_owner role, but since those are inside the database to be restored, they do not get the RESTORE permission.

So, you can make logins members of the dbcreator fixed server role or you can make one user the owner of a database and he should then be able to do a restore of that database.

To test the consequences I created a login TestLogin that is not a sysadmin for these tests.

TEST 1: Granted TestLogin the dbcreator server role and the public role in MyDatabase. (It is necessary that TestLogin have some rights inside the database to be restored.)

RESTORE DATABASE MyDatabase ... was successful.

NOTE: If the restored database did not already have TestLogin as a user, TestLogin lost access to the database once the restore was complete since the sysusers data was restored with the contents from the backup. So, TestLogin needs to exist in the source backup if this user is to retain some internal rights after the restore.

TEST 2: Revoked TestLogin from the dbcreator server role and the publicrole in MyDatabase. Made TestLogin the owner of the database.

USE MyDatabase
exec sp_changedbowner 'TestLogin'

RESTORE DATABASE MyDatabase ... was successful.

In other words, dbcreator can create NEW databases, but can only restore a database where it is already a user and still retain rights to that database. Hope that this helps you decide what to do.

1
source | link

One thing to note is that RESTORE is a command, not really a permission that can be granted. Which is why your GRANT failed.

In the SQL Server Books Online at https://msdn.microsoft.com/en-us/library/ms186858.aspx has pretty much said the same thing from SQL Server 2000 until now, namely:

RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database ... members of the db_owner fixed database role do not have RESTORE permissions.

If the dbo, not db_owner, seems confusing it is like this. One login owns the database, it may be sa or MyDomain\MyLogin. That login maps to the dbo user and should have rights to restore the database. Inside the database, many users may be in the db_owner role, but since those are inside the database to be restored, they do not get the RESTORE permission.

So, you can make logins members of the dbcreator fixed server role or you can make one user the owner of a database and he should then be able to do a restore of that database.

To test the consequences I created a login TestLogin that is not a sysadmin for these tests.

TEST 1: Granted TestLogin the dbcreator server role and the public role in MyDatabase. (It is necessary that TestLogin have some rights inside the database to be restored.)

RESTORE DATABASE MyDatabase ... was successful.

NOTE: If the restored database did not already have TestLogin as a user, TestLogin lost access to the database once the restore was complete since the sysusers data was restored with the contents from the backup. So, TestLogin needs to exist in the source backup if this user is to retain rights after the restore.

TEST 2: Revoked TestLogin from the dbcreator server role and the publicrole in MyDatabase. Made TestLogin the owner of the database.

USE MyDatabase
exec sp_changedbowner 'TestLogin'

RESTORE DATABASE MyDatabase ... was successful.

In other words, dbcreator can create NEW databases, but can only restore a database where it is already a user and still retain rights to that database. Hope that this helps you decide what to do.