Do not have permission to use 'dbo' when creating a view

I want to create a user with minimal required access to two databases within a server. The user will be used by someone from a third party integrating a Business Intelligence tool into our database. They have specified that they will only need to create views.

So I created a new user on the server, then used the database properties on each database to grant them the SELECT and CREATE VIEW privileges on that database.

I then logged in on SSMS using that user, and tried to create a view but I got a message saying that


'dbo' either does not exist , or I do not have permission to use it.

I tried to figure this problem out myself by trial and error but I just don't understand user permissions on sql server. What am I doing wrong and what extra/different steps do I need to take?

Remember, I don't want them to have more control than the minimum they need.

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

StackExchange Database Administrators Q#117271, answer score: 7