Re: Permissions on sp_OASetProperty

We have no users (other than dbo & guest) in the master database. How can
this be setup with the least amount of permissions being given.

Assuming SQL 2000 SP3 or above, you can use cross-database ownership
chaining to allow limited access to the sp_OA* procs and other potentially
dangerous master procs. Note that you should enable 'db chaining' in an
sa-owned database when only sysadmin role members are permitted to create
dbo-owned objects in that database.

1) From Enterprise Manager, uncheck 'only uses with sysadmin...' under
Management-->SQL Server Agent-->Job System and specify a Windows proxy
account when prompted. This proxy account is used as the OS security
context for access to external resources.

2) Change your user database owner to 'sa':

USE MyDatabase
EXEC sp_changedbowner 'sa'

3) Enable the 'db chaining' database option

EXEC sp_dboption 'MyDatabase', 'db chaining', true

4) Create a proc to wrap the needed functionality:

CREATE PROC dbo.usp_DoSomething
@MyData
AS
--initialization code, including sp_OACreate
EXEC sp_OASetProperty
@token,
@languageValue,
'CharSet',
'Shift-JIS'
--other code, including sp_OADestroy
GO

5) Create execute permission to users:

GRANT EXEC ON dbo.usp_DoSomething TO MyRole


This will provide an unbroken ownership chain because all of the objects
involved have the same owner (dbo) and both database owners are owned by the
same login (sa).

It is important that the proc be constructed in such a way as to prevent
ad-hoc execution of unintended commands.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Meenal Dhody" <meenal_dhody@xxxxxxxxxxx> wrote in message
news:%23EjS4BkaGHA.3992@xxxxxxxxxxxxxxxxxxxxxxx
We have a request to be able to execute the following from an application:

declare @languageValue varchar(1000)
exec sp_OASetProperty @languageValue, 'CharSet', 'Shift-JIS'

Currently the developer is unable to because they get an "EXECUTE
permission
denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
running this.

We have no users (other than dbo & guest) in the master database. How can
this be setup with the least amount of permissions being given.

I appreciate any suggestions.

thanks
Meenal








.

Relevant Pages

  • Re: Permission in database
    ... You can create database roles and assign object permissions to these ... EXEC sp_addrole 'Administrators' ... GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO Administrators ...
    (microsoft.public.sqlserver.security)
  • Re: Security - Stored Procedures
    ... This is known as ownership chaining. ... Permissions on indirectly referenced objects are not required. ... owned by the same login since database ownership determines the user/login ...
    (microsoft.public.sqlserver.security)
  • Re: Permissions!
    ... be aware that 'dbo' is a special user that has full permissions. ... Database permissions are not checked for the 'dbo' user so it serves no ... You will need individual logins/users so that SQL Server can identify users ... EXEC sp_addrole 'Role1' ...
    (microsoft.public.sqlserver.security)
  • Re: Select Permission Denied On Object
    ... The main consideration with cross database chaining is that the security implications aren't obvious. ... On the other hand, if data are not sensitive and direct selects are no big deal, go with select permissions. ... > I have a stored procedure in one database, ...
    (microsoft.public.sqlserver.security)
  • Re: Select Permission Denied On Object
    ... If I were to implement db chaining, what would be a good generic ... table in another database? ... Permissions on indirectly referenced objects are not needed as long as the ... I have a stored procedure in one database, ...
    (microsoft.public.sqlserver.security)