Switch to Another User’s Login using T/SQL

• muntasiriub

  SSCommitted

  Points: 1810

  More actions

  November 15, 2005 at 4:11 am

  #166636

  I would like to switch another User’s Login from T/SQL(Store Procedure) under same server.

  Is it possible….  

   

  For example,

                      

  …….

   

  CREATE PROCEDURE muntasir.Sp_myStoreProcedure

  AS

   

  SP_swithchTO ‘SA’,SA’ --------???

   

   

  EXEC sp_addlinkedserver

     @server = 'ersonName>RAFMersonName>',

     @provider = 'Microsoft.Jet.OLEDB.4.0',

     @srvproduct = 'OLE DB Provider for Jet',

     @datasrc = 'E:\Project06\ersonName>RAFMersonName>.mdb'

   

  EXEC sp_addlinkedsrvlogin 'ersonName>RAFMersonName>', 'false', 'raadmin'

   

  SELECT *

  FROM OPENQUERY(ersonName>RAFMersonName>,'select *

          from [Weekly Cell Performance] Where Week>40'

   

  EXEC sp_droplinkedsrvlogin 'ersonName>RAFMersonName>','raadmin'

  GO

  EXEC sp_dropserver 'ersonName>RAFMersonName>'

   

   

  sP_swithchTO ‘muntasir’,’muntasir’ --------???

   

   

  Exec muntasir.Sp_myStoreProcedure

   

   

   

  Can you please help me how to do it

  
  
  ..Better Than Before...

• noeld

  SSC Guru

  Points: 96590

  More actions

  November 15, 2005 at 8:21 am

  #603937

  in SQL 2000 there is an equivalent of what you need but the use is reserved for sysadmins only. Have a look at SETUSER in BOL

  Now, That said you don't need that if what you plan is to use it for the code posted. The solution is to use OPENROWSET or OPENDATASOURCE  and that will imply that you don't need to create a linked server because the connection parameters are supplied on the statements

   

  Cheers,

   

  
  * Noel

• muntasiriub

  SSCommitted

  Points: 1810

  More actions

  November 15, 2005 at 11:14 pm

  #604092

  Dear noeld ,

  Thx 4 Ur valuable Suggation!!!

  But Still i m facing same Problem like earlier!!! Follwing is my query:

  Select a.* from

  OPENROWSET

  ('Microsoft.Jet.OLEDB.4.0',

  'E:\Project06\RAFM.mdb';'admin';'',[Weekly Cell Performance] )

  as a

  Which works fine if i run it (only) as system Administrator Role!!

  other wise shows following error:

  Server: Msg 7415, Level 16, State 1, Line 1

  Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

  I donot want to give 'system Administrator Role' to a user who will run the query.....

  so can you please help me how to solve it

   

   

  
  
  ..Better Than Before...