Re: Finding/Accessing object when changing of Owner (dbo)

From: John K. (cgi_777@hotmail.com)
Date: 10/01/02


From: "John K." <cgi_777@hotmail.com>
Date: Mon, 30 Sep 2002 16:27:49 -0700


Hi Philippe,

The fast way to solve both of your problems is to create (or change
ownership using "exec sp_changeobjectowner 'owner.object', 'dbo'") all the
database objects (sprocs, tables, views, etc..) with an account that has the
fixed server role sysAdmin. That way you won't run into "ownership chain"
issues or have to specify the object owner in your queries.

Then add the users to the database with only the default public permission
(nothing else).
Next setup a user-defined database role that has execute permission to the
sprocs.
Last add the users to this user-defined database role.

Otherwise, if you don't want to do that you'll have to learn about
"ownership chains" and "fully qualified names" [ [ [ server. ] [ database ]
. ] [ owner_name ] . ] object_name. Which by the way, wouldn't take you too
long; your almost there anyway.

> ==> is there a way to change this and specify to SQL Server that the
> "standard" table ownere is another user than DBO?

Far as I know there isn't a way to change and/or specify to SQL Server that
the "standard" table owner is another user than DBO.

Regards,
John

"Philippe Mommens" <pmommens@synavant.com> wrote in message
news:eXPiHfGaCHA.2556@tkmsftngp09...
> 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: not creating tables as dbo anymore ?
    ... Database Owner (dbo) ... existing user ID in the database specified by database_name. ... with the login of the current connection. ...
    (microsoft.public.sqlserver.security)
  • Re: Change UDF Owner
    ... Does it exist in the master database? ... Users can be put in the dbo role, but dbo will still own objects ... concept of 'schema' as 'owner' rather than just schema as DDL is exploited ... to the appropriate Fixed Server Roles or whatever is applicable. ...
    (microsoft.public.sqlserver.security)
  • Re: Confused about dbo
    ... Jasper Smith (SQL Server MVP) ... > database within a SQL Server instance. ... > There are several ways you could have the username dbo. ... > the true owner of the database. ...
    (microsoft.public.sqlserver.security)
  • Re: conflicting object names in sql server 2000
    ... On the local database server when it does the select * from ... > michael.xxx Note that michael is the owner of the database. ... it will see if there is one owned by dbo. ...
    (microsoft.public.sqlserver.server)
  • Re: Change dbo
    ... I have a database where a user is defined as the dbo on a database. ... I tried using SQL Server Manger ... The login for this person maps to dbo. ...
    (microsoft.public.sqlserver.security)