Permissions on sp_OASetProperty

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> 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@.hotmail.com> wrote in message
news:%23EjS4BkaGHA.3992@.TK2MSFTNGP05.phx.gbl...
> 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
>
>
>|||Thanks alot for you suggestion- just a question or 2.
If the database is owned by an nt account and we use mixed mode for SQL
authenticate would I just set the proxy account up using the SQL NT login?
thanks
Meenal
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OBMImNsaGHA.1812@.TK2MSFTNGP04.phx.gbl...
can[vbcol=seagreen]
> 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@.hotmail.com> wrote in message
> news:%23EjS4BkaGHA.3992@.TK2MSFTNGP05.phx.gbl...
application:[vbcol=seagreen]
when[vbcol=seagreen]
can[vbcol=seagreen]
>|||I'm not sure why you ask this question. The proxy account is used as the
external OS security context when non-sysadmin users execute the sp_OA*
procs. That Windows account doesn't even need access to SQL Server.
Setting unless the code invoked via sp_OA* connects to SQL using a trusted
connection.
Unless you specify 'sa' as the user database owner, you'll need to add the
users to the master database and grant execute permissions on the sp_OA*
procs. It is technically possible to simply grant execute permissions to
guest or public but that's a very bad security practice.
Hope this helps.
Dan Guzman
SQL Server MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eEF4P6UbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> Thanks alot for you suggestion- just a question or 2.
> If the database is owned by an nt account and we use mixed mode for SQL
> authenticate would I just set the proxy account up using the SQL NT login?
> thanks
> Meenal
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OBMImNsaGHA.1812@.TK2MSFTNGP04.phx.gbl...
> can
> the
> application:
> when
> can
>