Stored Procedure's Included Call Fails

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure's Included Call Fails

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2012-06-22 : 11:18:56

I've got a Visual Studio 2010 web application that connects to our SQL Server 10.0.1600 (whatever version that is) using a connection string with "User Id=jp2code;Password=pwd1900;" (for example) in the connection string.

My web app can call any of the stored procedures I have written in my Production table. The Production table only shows 2 users: [public] and [guest].

First question: Which account (public or guest) are my stored procedures using?

I found a script for pulling all of the users in the database, and there are 11 listed. One is `jp2code` shown above, but `public` and `guest` are NOT in this list:

select p.name, p.type_desc, pp.name, pp.type_desc
from  sys.server_role_members roles
join sys.server_principals p on roles.member_principal_id = p.principal_id
join sys.server_principals pp on roles.role_principal_id = pp.principal_id

Typically, I would not care. Ignorance is bliss; however, I've run into a snag.

After a record is INSERTED or UPDATED through my code, I was making a seconds stored procedure call to send out a database email, passing in the record ID numbers from the last calls.

Seeing the inefficiency of "double dipping" into the database (create a record command, open the connection, call the command, create another email command, open the connection, call the command), I decided it would be much better to simply call the email procedure from within the other stored procedures.

The stored procedures for the INSERT and UPDATE commands created fine, but neither seems to be creating the email that is supposed to be called at the end.

The only hint I got was from a post on Stack Overflow where a guy referenced something about the user not having permissions.

I don't know if I am executing my stored procedures under the `jp2code` user, the `public` user, or the `guest` user ...or someone else! Let's just call that user "User X".

So, my main question is: How do I grant permission to "User X" to execute the email procedure after "User X" has completed the INSERT or UPDATE commands immediately before it?

~Joe

Avoid Sears Home Improvement (read why)

jp2code
Posting Yak Master

175 Posts

Posted - 2012-06-22 : 13:50:20

Solved.

I was not able to find out how to add user `jp2code`, but I was able to add `public` and `guest`.

Now the script executes.

I don't really understand why, though. Neither the INSERT nor the UPDATE scripts have user accounts granted to them.

~Joe

Avoid Sears Home Improvement (read why)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources