run store proc under a specified user

Question

run store proc under a specified user

fasttrack

SSCertifiable

Points: 6071
More actions
December 22, 2005 at 9:39 am

#169940

Hi guys.....
How can I run a store proc under a specified user (belong to administrators role). The store proc is executed by a scheduled job with vb.net
Thank for any useful suggestion

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Can you specify the user / password in the OLEDB connection string and use that to connect to SQL Server and then just EXEC the stored proc?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

When you say a job, do you mean a SQL Server Agent job or a Task Scheduler job? In the case of the latter you can easily specify the context of the job.

K. Brian Kelley
@kbriankelley

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

Ah - I read that to mean the scheduled execution of a VB.NET app.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

fasttrack SSCertifiable Points: 6071 More actions · Answer 4

I created a windows service which fires the store proc at specified time.

At the moment the connection is made with integrated secuity to avoid security problem.

There is a way to specify the user avoid usinging connection string?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

If it is a service, your connection string just needs to reflect integrated security. No username/password need be specified in it. In that case the connection to the database will be made under the context of the user that the service is running under.

K. Brian Kelley
@kbriankelley

fasttrack SSCertifiable Points: 6071 More actions · Answer 6

OK, I know that, as you write, the connection will be made under the context of the user that the service is running, but if I would like to force the user ......????!!!

This because the store proc should run under specific user (eg: a call to linked server is needed - this to mantain trust connection between servers)

thank again......

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

You have the service running under one user but you want to make the connection under a completely different user context? In that case you'd have to resort to impersonation and that's a programming question, not a SQL Server one.

K. Brian Kelley
@kbriankelley