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

Question

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.

Answer 1

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