SQL question

[Stetson]

This may lead to more questions and me having to buy some books on the subject but I have to ask.

Can I, in SQL Enterprise Server, set up a check to have a specific ID/PIN granted rights to a specific function?

Situation:  User will need rights to a higher level of functionality when Fill_machine_bills or jackpot_payout happens.  Currently it works 2 or 3 times then stops.  I don't know how this works exactly but the support for this form the applications team is intermittent.

[AZRedhawk44]

There's something called an application role.  I haven't read up on it since my SQL2000 tests about 8 or 9 years ago.

But from what I remember:
1. Set up a login ID with the specific enhanced permissions you need.
2. Set up a login ID for your application or end user to typically use.
3. Inside of the two stored procedures you mentioned (fill_machine_bills or jackpot_payout), you run a system stored procedure to "pivot" temporarily to another SQL acct.  I forget the name of the proc... something like sp_application_role or something like that.  I'll research it later and get back to you.

[GigaBuist]

According to the 2008R2 docs I pulled up there's the 'EXECUTE AS' T-SQL call too.  Might be worth looking into.

Never used anything of such nature myself.