Trying to add permission to a Role is failing

Question

I am trying to add a role into Create and Restore permissions and After researching online, it sounds like it should be as easy as using the GRANT statement. So far so good. However, my simple grant statement which looks like following:

GRANT CREATE, Restore ON dbo TO EventmanagerAdmin

Throws following error :

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'CREATE'.

I wish I had more information to debug but that is all it says. Some more details in environment and background:

• I am using SSMS 2012
• The role does exist with Execute permission on this DB
• I used script from List all permissions for a given role? to confirm that what permissions does the role have

Is there anything that I might be missing?

Answer 1

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.