Re: Execute Persmission denied on object 'sp_OACreate'

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 02/05/05 

Date: Sat, 5 Feb 2005 08:49:12 -0600

> How can get a user permissions to execute these stored procedures without
> assigning the dbo role. Surely I do not have to assign persmissions in
> the
> master database.

SQL Server doesn't check permissions on indirectly referenced objects as
long as the ownership chain is unbroken. For dbo-owned objects in different
databases, the databases involved need to be owned by the same user. If you
are running SQL 2000 SP3+, the databases involved also need to have the 'db
chaining' option enabled. See Cross Db Ownership Chaining
<adminsql.chm::/ad_config_8d7m.htm> in the SQL Server 2000 Books Online for
more information.

You can prevent ad-hoc execution of powerful master database procs while
providing the needed functionality as follows. Your user database needs to
be owned by 'sa' so that the ownership chain is unbroken for the dbo-owned
sp_OA* procs in the sa-owned master database. You'll also need to specify
the proxy account security context for non-sysadmin users from Enterprise
Manager under SQL Server Agent --> Job System. This account is used as the
OS security context for processes invoked by non-sysadmin users and should
have the minimum permissions needed by your application.

It is important to note that you should enable 'db chaining' in an sa-owned
database when only sysadmin role members have permissions to create
dbo-owned objects in that database. Also, ensure that parameters passed to
your proc is can't be used to inject unintended commands. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"WhiskyRomeo" <WhiskyRomeo@discussions.microsoft.com> wrote in message 
news:0D0422D2-0350-46B7-A963-7AAE1011EEC3@microsoft.com...
>I have a user who has execute permissions on a store procedure in a 
>database
> which in turns executes 4 stored procedures in the master database.  These 
> 4
> stored procedures execute a DTS package.
>
> Users, when given the dbo role, have no problem.  Users who only have the
> Public role but execute permissions on the parent stored procedure get the
> following error.
>
> Execute Persmission denied on object 'sp_OACreate', database 'master', 
> owner
> 'dbo'
>
> How can get a user permissions to execute these stored procedures without
> assigning the dbo role.  Surely I do not have to assign persmissions in 
> the
> master database.
>
> Any help would be appreciated.
>
> WR
>

Relevant Pages

  • Re: New user with no permissions can see and execute system stored procedures...
    ... Best regards ... But I can still see system views and stored procedures (though not system ... and map it to a database user and set its default schema to dbo. ... I have only tried to execute sys.sp_catalogs, but in my opinion a new ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • SQL 2000 Windows Authentication - Same User Multiple Groups
    ... view-level permissions such that we can permit/deny a database action ... Execute permission on UpdateResearch to only IT (and explicitly Denied ... Windows group and we have assigned the appropriate group permissions on ...
    (microsoft.public.sqlserver.security)
  • Re: Effective Permissions Error with Domain User
    ... I set the database compatibility to 2005. ... server profile trace and found that it was calling the Execute As User. ... This leads me to believe it is some sort of permissions issue. ... Did you get these database from SQL Server 2000 by using a RESTORE command? ...
    (microsoft.public.sqlserver.security)
  • Re: how to secure a sql 2005 database?
    ... that's why i rather keep my stored procedures in my own source code, then pass it thru to sqlexec to execute. ... its like quickbooks turning over their internal structures, which they really don't, but provide an api to export data out. ... so are you saying that i can't create a user and make that user the only valid user to get into the database, and unless you know the password, even admins, you cant open the database at all. ...
    (microsoft.public.sqlserver.setup)