Run as user in SQL 2000 errors in 2005.

• sgtwilko

  SSC Veteran

  Points: 271

  More actions

  February 15, 2008 at 8:59 am

  #186766

  Hi,

  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?

  Thanks,

  Ian.