Issues with privileges

I am having trouble with providing the minimum security to a user. After issuing the following:

GRANT EXECUTE ON SCHEMA :: DBO TO skillsnetuser;

I test the permissions with

exec as login = 'skillsnetuser'

exec prcElmtList 1, 1, 102268

revert;

and receive this message

Msg 229, Level 14, State 5, Line 2

SELECT permission denied on object 'Org', database 'SNAccess_Dev', schema 'dbo'.

The principal that owns the dbo schema is dbo and is the principle for all procedures and tables in that schema.

What can I do to shed some light on what is causing this access problem?

Are the objects 'Org' and the procedure prcElmList defined in the same database ?|||Yes, Org is a table that prcElmList accesses|||

My first guess is that ownership chaining is broken for some reason. Can you please include the portion of the SP body that access the table? We can take a look to it and try to determine why ownership chaining is broken in this case.

I would also like to suggest migrating to one of the other mechanisms we have available on SQL Server 2005 that allow controlled access to resources via a module (i.e. SP) execution:

* EXECUTE AS – For more information you can visit Context Switching in BOL (http://msdn2.microsoft.com/en-us/library/ms188268.aspx)

* module signing: Module signing in BOL (http://msdn2.microsoft.com/en-us/library/ms345102.aspx)

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

IF NOT EXISTS (SELECT

o.OrgID

FROM

Org o

WHERE

o.OrgID = @.piOrgID

AND o.Active = 1

AND o.BeginDate < GetDate()

AND (o.EndDate > GetDate() OR o.EndDate IS NULL) )

BEGIN

RAISERROR ('The passed OrgID is invalid or inactive.' ,17 ,1)

RETURN -2 -- OrgID not valid or active

END -- IF NOT EXISTS

|||

I tried a similar scenario, and it seems to work for me, and as your sample code doen’t involve any dynamic SQL I am not sure what I may be missing. Can you give us some more information?

I would like to ask you to run the following query:

SELECT name, principal_id, schema_id

FROM sys.objects WHERE

name LIKE 'prcElmtList'

OR name LIKE 'Org'

I would also like to ask you if you are using a database in backwards compatibility mode (and if so, what is the compatibility level you are using) and if you are using any options for the stored procedure declaration (such as NOT FOR REPLICATION, EXECUTE AS, etc.), and if there are more objects involved in the chain other than the SP and the table.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

SELECT name, principal_id, schema_id

FROM sys.objects WHERE

name LIKE 'prcElmtList'

OR name LIKE 'Org'

--Results

name principal_id schema_id

Org NULL 1

prcElmtList NULL 1

--For the procedure generation script

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

WITH RECOMPILE

--General Properties of the procedure showned by the GUI

Execute as caller

Schema dbo

System object False

ANSI NULLS True

Encrypted False

For replication False

Quoted identifier True

Recompile True

--Database Options showned by the GUI

Compatibility Level SQL Server 2005 (90)

All Miscellaneous Settings are False except Parameterization which is Simple

|||

At this junction I have added with execute as owner to the procedures,

this provides the functionality I was expecting to be present....

|||

Sorry for the late response, I have been trying to repro why ownership chaining didn’t worked for this particular scenario, I tried creating a small repro similar to the one you described, but so far I have no luck (my repro test is able to access the table as expected).

If you consider it is a bug in the product, I would recommend following the instructions described on the “Tips on using SQL Server Security forums” page to report it (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1). We will probably need more details than the ones we have right now to continue the investigation.

I am glad you were able to find a solution for the problem. Please, let us know if you have any further questions or feedback; we will greatly appreciate your feedback on the EXECUTE AS feature.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine