June 11, 2010 at 1:27 am
I have an application that uses an application role to log on to a SQL 2008 database. I am getting the usual messages about needing to be in the sysadmins group to execute sp_OACreate but the normal scripts to fix this problem don't seem to work with application roles. Anyone got any ideas?
Gav
June 11, 2010 at 5:50 am
by normal scripts to fix this problem do you mean the EXECUTE AS clause?
I'm not sure what you mean; you should be able to create a procedure with the EXECUTE AS clause to do the sp_oaCreate stuff and return data or a value, or you could move it over to a CLR function, it depedns on what you are doing with sp_OACreate.
Lowell
June 11, 2010 at 5:57 am
Usually, because sp_OACreate can only be run from a sysadmin account, we have to run a script like the following to allow a user to execute it. In this case VimsApps is an application role.
--Ensure user database is owned by 'sa' and cross-database chaining
USE MyDbName
GO
EXEC sp_changedbowner 'sa'
EXEC sp_dboption 'MyDbName', 'db chaining', TRUE
GO
--grant execute permission on only the specific procedure required
USE VIMS_HU
GO
GRANT EXEC ON dbo.xpPutPalletOnQueue TO VimsApps
GO
--enable OLE automation procedures
sp_configure 'show advanced options', 1
GO
EXEC sp_configure allow_updates, 0
GO
RECONFIGURE
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
EXEC sp_configure allow_updates, 1
GO
I cannot get this to work for me.
June 11, 2010 at 6:44 am
what i was getting at is the procedure you menthioned, dbo.xpPutPalletOnQueue
if the procedure features EXECUTE AS in it, It runs with different priviledges, and your application role wouldn't need priviledges with sp_configure 'Ole Automation Procedures', 1;
EXECUTE AS requires the user to exist in the database(so you can't say EXECUTE AS 'sa') , so I'm pretty sure this will work for you:
creating a login and a user with elevated priviledges.
see if this works with your sp_OaCreate stuff:
--create our super user
CREATE LOGIN [superman] WITH PASSWORD=N'NotARealPassword',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'
GO
--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [superman] DISABLE
GO
USE [SandBox]
GO
CREATE USER [superman] FOR LOGIN [superman]
GO
USE [SandBox]
GO
EXEC sp_addrolemember N'db_owner', N'superman'
GO
--now create our procedure that runs under special priviledges instead of as the caller.
--this is just an example, replace with your proc and add the WITH EXECUTE AS clause.
CREATE procedure [dbo].[sp_find]
@findcolumn varchar(50)
WITH EXECUTE AS 'superman'
as
begin
set nocount on
select sysobjects.name as TableFound,syscolumns.name as ColumnFound
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'
and (syscolumns.name like '%' + @findcolumn +'%'
or sysobjects.name like '%' + @findcolumn +'%' )
order by TableFound,ColumnFound
end
Lowell
June 11, 2010 at 11:57 am
Thanks for this, I should get a chance to try it on Monday or possibly over the weekend.
Gav
June 11, 2010 at 2:02 pm
I tried it and was very hopeful but ended up with the same error message. I created the 'superman' super-user exactly as you demonstrated. I modified my procedure with the EXECUTE AS and got the same result.:(
Here is the code of my procedure and it is followed by the error message.
CREATE PROCEDURE [dbo].[xpPutPalletOnQueue]
@sMessage VARCHAR(255) --Message of up to 256 CHARacters to be passed to MQ Series
WITH EXECUTE AS 'superman'
AS
--Declare variables for object reference and error values
DECLARE @iObjectRef INT
DECLARE @iHReturn INT
--Create an instance of the MqInterface DLL (MqPut)
EXEC @iHReturn = sp_OACreate 'MqProductiondll.MqPut',@iObjectRef OUT
IF @iHReturn <> 0
BEGIN
EXEC spDisplayOAErrorInfo @iObjectRef, @iHReturn
RETURN
END
--Call the method PutOnQueue and pass the message as an argument
EXEC @iHReturn = sp_OAMethod @iObjectRef, 'PutOnQueue', NULL, @sMessage OUTPUT
IF @iHReturn <> 0
BEGIN
EXEC spDisplayOAErrorInfo @iObjectRef, @iHReturn
RETURN
END
--Destroy the instance of the MqInterface DLL
EXEC @iHReturn = sp_OADestroy @iObjectRef
IF @iHReturn <> 0
BEGIN
EXEC spDisplayOAErrorInfo @iObjectRef, @iHReturn
RETURN
END
I get the following error message 3 times for every attempted execution of the procedure.
__________________________________________________________________________
-214721911 The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
__________________________________________________________________________
Obviously, any further guidance would be gratefully received.
Gav
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply