Search code examples
sql-serversql-server-2012privileges

How do I grant sql permission to an object in a different schema as dbo?

This 

GRANT EXECUTE ON OBJECT::[test].[someTable] TO [MyUser] AS [dbo];

fails with error

Msg 15151, Level 16, State 1, Line 1 Cannot find the object 'someTable', because it does not exist or you do not have permission.

If I remove the AS [dbo] part it works fine. Can someone explain why this is and the implication of removing the as dbo part?

This is for SQL Server 2012

Solution

  • AS with GRANT - Specifies a principal from which the principal executing this query derives its right to grant the permission (from MSDN). So it means [dbo] dosn't have permission to GRANT something on SCHEMA [test]. I think you should run this before your query:

    GRANT EXECUTE ON SCHEMA::[test] TO [dbo] WITH GRANT OPTION;