Finding/Accessing object when changing of Owner (dbo)

From: Philippe Mommens (pmommens@synavant.com)
Date: 09/30/02 

From: "Philippe Mommens" <pmommens@synavant.com>
Date: Mon, 30 Sep 2002 11:56:19 +0200

Hello,

I am trying to implement the following architecure and I am facing some
difficulties.

I would like to have a
- A user "TAB_USER" that is the owner of the tables of my system
- A user "PROC_USER" that is the owner of the stored procedure and function
of my system
- The end-users that will access the system will have a specific login like
"USER1", "USER2", ...

The required privileges (Select, Insert, Update, Delete) are granted to the
user "PROC_USER" in order to allow the stored procedure to access the data.
This means that when I am connected with the user PROC_USER I have no
problem executing my procedure to access the data.

The end-users have Execute privilege on the stored proc but NO specific
right are given to access the tables.
==> The end-users can execute the procedure but I have an error "Msg 229,
Level 14, State 5 - permission denied on object" when the procedure try to
select from a table.
I can solve this by granting required privilege to the end-users but the
consequence of this is that if the end-users connecting to the database with
their user thru an ODBC connection or with SQL Analyzer, can do any
operation they want on the table (provided that they have the access right).

On the security point of view, I want that the users ignore from which table
the data are retrieved, so that's the reason I am passing thru a stored
procedure.

An other issue I have is that I have to give explicitely the name of the
table owner to be able to query the data, this means that the procedure
canot just do a "select * from tableX", we must do "select * from
TAB_USER.tableX", in this condition the user that will own the table is not
customizable. When using the user DBO as table owner, SQL Server search for
the existance of the table in the current schema then it search in the DBO
schema.
==> is there a way to change this and specify to SQL Server that the
"standard" table ownere is another user than DBO?

Thank for any suggestion,
Philippe
pmommens@synavant.com

Relevant Pages

  • Re: ADP: Cant use stored procedure on remote SQL server
    ... Not only I use dbo everywhere but I ... If you don't mention the owner when creating a new stored procedure, view, ... BTW in the database window, all the stored procedures are followed by ... Check also the owner of the SPInc stored procedure. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Stored Procedure Disappearing
    ... The owner of the sp and all of its component functions is dbo. ... summary report which obtains data from a large stored procedure containing ...
    (microsoft.public.access.reports)
  • Re:Stored Procedure Question
    ... When you execute a SP without any owner name by default ... SQL assumes it to be dbo. ... the stored procedure fire following query. ...
    (microsoft.public.sqlserver.security)
  • Re: object reference in stoed proc
    ... If you don't qualify the object sql server will first look for the object ... as such it will eventually try dbo. ... implications by not qualifying the objects with the owner. ... all object refences dbo by default anyway within a stored procedure? ...
    (microsoft.public.sqlserver.security)
  • Re: User rights problem
    ... You cant give permission to developer to create a stored procedure under the ... db_owner rights and execute the below command to change the owner to dbo. ... This will make the object owner to dbo. ...
    (microsoft.public.sqlserver.security)