Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. dbo schema functions as it would normally, has access to everything
  2. schema1 schema has access to only schema1 objects
  3. if a schema1 view or stored procedure accesses data in tables owned by dbo, the permissions chain appropriately
  4. user1 has access to schema1, and nothing else; except in the case of #3

Here's what I've tried:

  1. Create a user1 user mapped to a test login with a random password
  2. Created a couple tables in the dbo schema with some test data in them
  3. Created a schema1 schema
  4. 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

results in:

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.

share|improve this question
    
Refer to my answer : How To Hide Schema from user Let me know if you need any more clarification. –  Kin Nov 18 '13 at 20:32
    
Thanks! I will take a look after a meeting. –  Jordan McGuigan Nov 18 '13 at 20:34
1  
This seems to made it more clear to me. I think the problem was I had the owner of Schema1 set to User1, instead of dbo. I set Schema1 to be owned by User1, adapted and followed the question, and I was able to get something working. You may propose an answer to the question if you want. Thanks! –  Jordan McGuigan Nov 18 '13 at 21:33
    
Glad that it helped you. –  Kin Nov 18 '13 at 21:37

1 Answer 1

up vote 1 down vote accepted

The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.

Below is an example that will explain you in detail

use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';

use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go

-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go

create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go

Now test :

--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A

-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Now create Stored Procedures :

CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
    select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as 
    select * from schemaA.TableA

Now Grant execute permissions to UserA on schemaB's SP

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA] 
go

Test it .. to see if UserA is able to run SP from schemaB. This will PASS

EXECUTE AS LOGIN='UserA';
    Exec SchemaB.proc_SelectUserB;
    revert;
go
--- Kin-A

But UserA wont be able to see data from SchemaB

EXECUTE AS LOGIN='UserA';
    select * from SchemaB.TableB
revert;
go

--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:

EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go

Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc role and they will inherit all the permissions granted to that role.

GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go

If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc] 
go

This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.

Below will explain the permission hierarchy :

enter image description here

share|improve this answer
    
Just to reiterate, it's important that both schemas are owned by the same user. That was the main issue I was having. –  Jordan McGuigan Nov 18 '13 at 21:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.