Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

create procedure with execute as Expand / Collapse
Author
Message
Posted Monday, August 13, 2012 12:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:44 PM
Points: 25, Visits: 79
I have stored procedure which includes bulk insert, activating and deactivating the access to xp_cmdshell and executing xp_cmdshell. I am trying to get this procedure to run without the user being in the sysadmin fixed server role. I got bogged down in fixed server roles and proxy users for xp_cmdshell and then saw a reference to creating the procedure using the with execute as option. I have re-created the procedure using the with execute as <use in sysadmin role> and granted the non-sysadmin user the rights to execute the procedure. When I try to run it logged in as the non-sysadmin user it errors out on the bulk insert statement as follows:

You do not have permission to use the bulk load statement.

Everything I've seen about using the execute as option when creating the stored procedure indicates that the procedure will be run with the permissions of the specified execute as user. The specified user is in the sysadmin role and can run the procedure if I log in as him. I would appreciate any suggestions as to what I am doing wrong.

Thanks
Chuck
Post #1344338
Posted Tuesday, August 14, 2012 10:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
From my perspective you should be looking at turning this process inside-out and using PowerShell to execute whatever you're doing with xp_cmdshell, bcp.exe to load your file and sqlcmd or the ADO.NET classes to execute T-SQL. A new SSIS package would also be a great candidate to replace the T-SQL process. If you want to hear more about either of those options that let me know.

If you want to continue with everything in T-SQL:

Database Users are not the same as Server Logins. Database Users are not included in the sysadmin Fixed Server Role, Server Logins are. BULK INSERT requires server-level permissions.

In order to accomplish this task I would recommend you look into signing your procedure with a certificate based on a user in the master database with the server-level permissions necessary to call BULK INSERT as well as exec xp_cmdshell.

Signing Stored Procedures with a Certificate (SQL Server 2005)


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1344862
Posted Wednesday, August 15, 2012 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:44 PM
Points: 25, Visits: 79
My background is a good bit of SQL scripting mostly self taught and enough vb scripting to get me by. I am willing to learn anything that will help processes run better. I'm reviewing and testing the link you provided but would love to hear about options. Thanks for your help

Chuck
Post #1345269
Posted Wednesday, August 15, 2012 9:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
Chuck 17099 (8/15/2012)
My background is a good bit of SQL scripting mostly self taught and enough vb scripting to get me by. I am willing to learn anything that will help processes run better. I'm reviewing and testing the link you provided but would love to hear about options. Thanks for your help

Chuck

Sorry but I must start with a disclaimer: we're talking about security and division of responsibility, i.e. architecture, so one is not necessarily better or worse. Further to that, these are general suggestions. These solutions may not be conducive in your environment, depending on logistics, security scheme, and many other variables I am not privy to.

BULK INSERT and xp_cmdshell are powerful tools but they arrive with lots of baggage in the area of security as you have learned. Many people simply blast through the roadblocks and start adding people to the sysadmin Role or setting up the proxy and granting exec on xp_cmdshell without understanding the ramifications of doing something like that, so kudos to you for doing better.



For backend processing, i.e. where user-interaction is not required, the tools can be quite useful. But, when wanting to expose their functionality to interactive users it can get dicey.

Alternative 1

Create a PowerShell script that:

- leverages the .NET SqlClient library to execute stored procedures as needed
- in place of BULK INSERT use bcp to load flat files into database tables
- in place of xp_cmdshell since we're in a PowerShell context you can simply execute what is needed using PowerShell native commands or the same commands/executables you were calling using xp_cmdshell

Excerpt from the Permissions section in bcp Utility (SQL Server 2005):

A bcp in operation minimally requires SELECT/INSERT permissions on the target table.


Your users can then execute the PowerShell script.

Alternative 2

Create an SSIS package that:

- uses the Execute SQL Task to execute stored procedures as needed
- in place of BULK INSERT use a Data Flow Task to load flat files into database tables
- in place of xp_cmdshell use the Execute Process Task to execute cmd-shell commands or other executables

Your users can then execute the SSIS package directly using dtexec.exe.

Alternative 3

Setup an asynchronous Service Broker (SB) queue that your users can insert a 'request to execute said proc' into. The worker process that services that queue would then asynchronously execute your proc in a different security context, one with the permissions necessary to run BULK INSERT and xp_cmdshell.

This option is not interactive, i.e. the caller would receive confirmation when inserting into the SB queue that a message was added to the queue but they would not necessarily know the outcome of the SB worker process unless they checked back later.

--

In closing, signing a proc that uses BULK INSERT and xp_cmdshell so an interactive user can successfully exec the proc while still maintaining a very limited set of database permissions has merits and demerits. The same applies to the options I show above.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1345369
Posted Wednesday, August 15, 2012 10:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:44 PM
Points: 25, Visits: 79
Thank you. I will experiment with each of the options you have suggested.

Chuck
Post #1345374
Posted Wednesday, August 15, 2012 10:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
My pleasure. If you have any issues or doubts after you start experimenting with any of the options feel free to post back and I'll do my best to answer your questions.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1345378
Posted Wednesday, August 15, 2012 12:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Just to chime in, I'd go with SSIS. It's designed to do this kind of work. It's easy to get started in it (at least, I remember it being so when I started on it), and it does a good job.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1345429
Posted Wednesday, August 15, 2012 1:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:44 PM
Points: 25, Visits: 79
I have experimented with certificates and took a quick look at Power Shell and SSIS and I think SSIS is probably my best options. I found an artical on using SSIS with examples. I wants be to launch Business Intelligence Development Studio [which is basically Visual Studio] and create a new project and select Business Intelligence Projects project type. That type is not one that I have on my list. I tried installing Business Intelligence Development Studio from my SQL 2008 R2 DVD and it is still not on the list. Is this something I can install seperately as a plugin?

Never mind. I figured it out
Post #1345513
Posted Thursday, August 16, 2012 6:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
What edition of SQL Server are you using?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1345878
Posted Thursday, August 16, 2012 11:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:44 PM
Points: 25, Visits: 79
I'm using 2008 r2 but I figured it out. The path I was given wasn't quit right. Sorry for bothering you
Post #1346128
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse