[dba-SQLServer]SS7 - 80040e09 permission denied (strange) - Take 2

I would double check the owner of the stored procedure. You might want 
to try and login as the dbo of the db2 and re-create the sproc if it is 
giving you problems.  At first it seemed almost as if you were 
describing symptoms of dynamic sql behavior.  but that is not the case 
here... generally if the creator of the sproc does not have rights to 
said table, then you will have this type of problem... btw.. you may 
also want to double check the rights for the login user on your 'linked 
server' or 'remote server' settings

Joe Rojas wrote:

>Hi All,
>
>I am using SS7 with a couple of web based front ends (ASP).
>
>I have one database that I used to store information that all the other
>databases will use, e.g. login information.
>I only have one user in each database which is the IIS Webserver anonymous
>user.
>This user only has exec permissions on stored procedures, i.e. no direct
>access to tables.
>In the 'Global' DB this user has no permissions to anything and as of right
>now, this DB only has one table, tblLogins.
>
>DB1 has a stored procedure that accesses this 'Gobal' database to check if
>the user has given valid login credentials (see below) and everything works
>perfectly.
>
>DB2 has a stored procedure that does that same as above and the coding of
>the SP is identical to that of DB1. When run from DB2 I get an error message
>from the web page that reads: SELECT permission denied on object
>'tblLogins', database 'TNCOGlobal', owner 'dbo'.
>
>I checked both databases, both web apps, and all folder permissions to find
>any differences and have come up with bunk! Any ideas as to why this works
>with one DB and not the other?
>
>Thanks,
>JR
>
>CREATE PROCEDURE dbo.sp_checklogin
>@user varchar(30),
>@pass varchar(20),
>@FL tinyint OUTPUT
>AS
>DECLARE @pass2 varchar(20)
>
>SET NOCOUNT ON
>SELECT @pass2 = Password, @FL = FirstLogin FROM TNCOGlobal.dbo.tblLogins
>WHERE UserName = @user
>SET NOCOUNT OFF
>
>IF (@pass2 Is NULL)
>BEGIN
>        SET @FL = 3
>        RETURN
>END
>
>IF (@pass <> @pass2)
>BEGIN
>        SET @FL = 2
>END
>
>RETURN
>  
>


-- 
-Francisco