I need to limit access to a particular user, but they still need to be able to see the data in tables owned by dbo.
I'm trying to do the following:
- dbo schema functions as it would normally, has access to everything
- schema1 schema has access to only schema1 objects
- if a schema1 view or stored procedure accesses data in tables owned by dbo, the permissions chain appropriately
- user1 has access to schema1, and nothing else; except in the case of #3
Here's what I've tried:
- Create a user1 user mapped to a test login with a random password
- Created a couple tables in the dbo schema with some test data in them
- Created a schema1 schema
- Created a schema1.get_profiles that selects from a view called schema1.profiles which accesses data in dbo.people, dbo.taglinks, and dbo.tags
However, using the following statement while logged in as user1:
EXEC get_profiles 1
The SELECT permission was denied on the object 'tags', database 'schema_test', schema 'dbo'.
I have tried
WITH EXECUTE AS OWNER and can't begin to understand how "ownership chaining" is supposed to work.
I have also tried
GRANT EXECUTE ON SCHEMA::schema1 TO user1
GRANT INSERT ON SCHEMA::schema1 TO user1
GRANT SELECT ON SCHEMA::schema1 TO user1
GRANT UPDATE ON SCHEMA::schema1 TO user1
GRANT VIEW DEFINITION ON SCHEMA::schema1 TO user1
but I get the following error (despite being a user with dbo-level access):
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
What I need is user1 to be able to access the data via the stored procedures I give it, and nothing else.
Additionally, this is intended to eventually live on an existing SQL Azure database, but I'm testing against a local dummy database first.