SQL Server – Adding Permission to a Role Fails

permissionssql-server-2012ssms

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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server 2005 Easy way to add all users to a database role

Adam Machanic has a blog post on replacing xp_execresultset which in SQL 2000 did what you want: Replacing xp_execresultset in SQL Server 2005

SQL Server 2005 – Fixing ‘Do Not Have Permission to Use dbo’ Error When Creating View

Your user should have the permission to modify the dbo schema. You can accomplish that by assigning the permission to a role the User is a member of:

So its like:

CREATE ROLE Limitedaccess; 
GO 

GRANT CREATE VIEW TO Limitedaccess; 
GO 

GRANT SELECT ON SCHEMA::dbo TO Limitedaccess; 
GO 

CREATE USER user WITHOUT LOGIN; --- this would be the user here
GO 

EXEC sp_addrolemember 'Limitedaccess', 'User'; 
GO

Once permission is granted, re-run the previous CREATE VIEW statement. It will now succeed.

GRANT ALTER ON SCHEMA::dbo TO Limitedaccess; 
GO

Best Answer

Related Solutions

Sql-server – SQL Server 2005 Easy way to add all users to a database role

SQL Server 2005 – Fixing ‘Do Not Have Permission to Use dbo’ Error When Creating View

Related Question