Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Run as user in SQL 2000 errors in 2005. Expand / Collapse
Posted Friday, February 15, 2008 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 24, 2015 2:03 AM
Points: 32, Visits: 177

We are a software house that produces a product based on SQL server. We use a strong encryption for stored procs, functions, triggers and the like so our code can't changed or viewed.

All procs are run as one user (from a website). This user has very limited permisions (eg, not able to get outside of the database and certainly not able to run xp_cmdShell).

Inside several of these procs we have the following to change the user to one that is able to do more, and does have permission to run xp_cmdShell.

exec OPENDATASOURCE('SQLOLEDB', 'User ID=Dave;Password=Dave').master.dbo.xp_cmdShell @cmd

As the proc is encrypted, and you are not able to view the password we believe this to be a secure way of controling permisions without limiting what the application does.

This has been working fine in SQL 2000 for the last 6 years, this code does not work under SQL 2005 but only in triggers. We get the dreaded "Transaction Context in use by another session".

We can't use 2005 specific features as 99% of our customers are running on 2000.

Any ideas on this problem?



Post #456347
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse