Re: Help pls: Not allow users access to tables, stored procedures


"Ben" <ben_1_ AT hotmail DOT com> wrote in message news:94929023-4128-4237-ADE6-A4BFC1D01003@xxxxxxxxxxxxxxxx
It looks like I may have been wrong with a previous statement. Not all
tables are owned by dbo. Some are owned by another user. Wouldnt a dbo have
privilege on another user's tables?

If not, it there a way to force this to work WITHOUT giving select
permissions on the tables not owned by dbo?



Dbo has privileges, but the stored procedure is not run as dbo. It is run as the caller, only with certan permission checks skipped. Ownership chains only help you when the owner of the procedure is the same as the owner of the table.

In SQL 2005 you can use the EXECUTE AS option to cause the stored procedure to be invoked using the privileges of the procedure owner instead of the privileges of the caller. Erland explains all:

Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html

David

.

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)
  • Finding/Accessing object when changing of Owner (dbo)
    ... A user "PROC_USER" that is the owner of the stored procedure and function ... The end-users that will access the system will have a specific login like ... user "PROC_USER" in order to allow the stored procedure to access the data. ... the existance of the table in the current schema then it search in the DBO ...
    (microsoft.public.sqlserver.security)
  • 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: 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)