Running an executable from within a stored procedure
-
I need to have an executable run from within a stored procedure. The user running the executable will not be a member of the sysadmin role. Trolling around the web, I found the following post:
People!
Forget about xp_cmdshell (and SQL Server Agent proxy account)
for ordinary users for good! Much simpler and safer it will be:
use pubs
GO
EXEC sp_addlogin 'asd', 'pwd', 'pubs'
GO
EXEC sp_grantdbaccess 'asd', 'asd_pubs'
GO
CREATE procedure asdShell
as
declare @obj int
exec sp_oacreate 'WScript.Shell', @obj out
exec sp_oamethod @obj,
'Run("c:\mssql7\binn\bcp pubs..authors out d:\au.txt -c -S(local) -Usa -Ppwd", 0)'
exec sp_oadestroy @obj
return
GO
GRANT EXECUTE ON asdShell TO asd_pubs
GO
The only OS/shell damage user asd_pubs can cause is that inside of the sproc.
Not giving execute access to xp_cmdshell appealed to me, so I set up a test SP. Before I gave the user execute access to it, I got the following error message, which I expected:
Server: Msg 229, Level 14, State 5, Procedure stp_ExtractCreateFlatFiles, Line 2
EXECUTE permission denied on object 'stp_ExtractCreateFlatFiles', database 'test', owner 'dbo'.
Stored Procedure: test.dbo.stp_ExtractCreateFlatFiles
But after giving the SP execute access to it, I now get the following error message, which I did not expect:
Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 7
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
Server: Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 8
EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.
Server: Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line 9
EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.
Stored Procedure: test.dbo.stp_ExtractCreateFlatFiles
It seems fairly clear that I need to give execute access to these three stored procedures to the user as well. Of course, that user doesn't exist in the master database, and I'm trying to do this through roles, not users, and since one of the databases is in a test instance (on the same server), it's constantly being backed up from production and overlaid in test. So what seemed like the best way to give the 'least permission' is rapidly becoming way more complicated than I really care to deal with.
So my questions are:
1. Is it worth trying to do it using the sp_oa* approach? Is it that much safer than xp_cmdshell?
2. Is there a way around setting the user up in the master database? Can I use one of the server roles?
3. Why was this alternative presented as being preferable to using xp_cmdshell? (It wasn't on this site.)
4. Is there a better way to accomplish running an executable from within a stored procedure?
And just to make it clear, we don't have a DBA here, just programmers running amok
So once you wake up from the dead faint that occurred when you found that out, I'd appreciate whatever help you can offer.Thanks,
Mattie
-
The Ole Automation SPs are actually XPs (DLLs) and require that the user be in the sysadmin role. I don't think that granting execute privileges on them will alter that.
You may want to think about doing this the other way around - execute the stored procedure from the external exe
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply