Calling a SPROC from a SPROC in another database

Hi,
Is there a way to EXEC SPROC A in db B from SPROC A in db A?
I've tried USE, but you can't use that in a SPROC. I've tried prefixing the
database name to the SPROC call (ie. db_B..sproc_A) but that just returns me
an error:
Server: Msg 2812, Level 16, State 62, Line 18
Could not find stored procedure 'db_B..sp_addrolemember db_owner, xxxx99'.
As you can see my SPROC is in db_A and I'm trying to add a user to a role in
db_B using sp_addrolemember.
So what do I do? Any ideas?
TIA,
EricCould you please explain this situation? You're setting privileges for a use
r
in one database from another database? Why?
ML|||In short, because that's what I have to work with. The general theory behind
the madness was that one database was going to be the central control of
security for all databases on the server. The db_A database is the main link
between the website and all the databases on the server. The project
specifications provided a table that contains more detailed information abou
t
the users referenced by their SQL login ID/name (ie. first name, last name,
phone number, etc.)
So... any thoughts?
"ML" wrote:

> Could you please explain this situation? You're setting privileges for a u
ser
> in one database from another database? Why?
>
> ML|||Can you show the exact call you try? Should be:
EXEC db_B.dbo.sp_addrolemember 'db_owner, 'xxxx99'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric D." <EricD@.discussions.microsoft.com> wrote in message
news:1CC7E5C3-73AE-451A-911B-BD4D7E8B38A2@.microsoft.com...
> Hi,
> Is there a way to EXEC SPROC A in db B from SPROC A in db A?
> I've tried USE, but you can't use that in a SPROC. I've tried prefixing th
e
> database name to the SPROC call (ie. db_B..sproc_A) but that just returns
me
> an error:
> Server: Msg 2812, Level 16, State 62, Line 18
> Could not find stored procedure 'db_B..sp_addrolemember db_owner, xxxx99'.
> As you can see my SPROC is in db_A and I'm trying to add a user to a role
in
> db_B using sp_addrolemember.
> So what do I do? Any ideas?
> TIA,
> Eric|||Hey Tibor,
This is the statement I execute:
db_B.dbo.sp_addrolemember 'db_owner' , 'xxxx99'
I'm still getting the same error.
Now since this SPROC is being called from another SPROC which is called from
cfml page, I'm wondering if it's a coldfusion problem. The reason I say this
is because logged in as a WIN NT domain user, Query Analyzer runs this
statement without errors.
Thoughts?
TIA,
Eric
"Tibor Karaszi" wrote:

> Can you show the exact call you try? Should be:
> EXEC db_B.dbo.sp_addrolemember 'db_owner, 'xxxx99'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Eric D." <EricD@.discussions.microsoft.com> wrote in message
> news:1CC7E5C3-73AE-451A-911B-BD4D7E8B38A2@.microsoft.com...
>|||It would be best if the application would set user privileges to the central
security database directly.
But I think the actual problem right now might be insufficient privileges
for the user/role you're using to connect to the first database (db_A) - thi
s
user should have appropriate privileges on the stored procedure in the secon
d
database (db_B).
You've proven this by successfully executing the procedure when logged in as
a sufficiently privileged NT user.
ML|||You need to put EXEC in front of it all... See my example.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric D." <EricD@.discussions.microsoft.com> wrote in message
news:73A69B32-612B-45AE-A6DA-57F350D0ADD0@.microsoft.com...
> Hey Tibor,
> This is the statement I execute:
> db_B.dbo.sp_addrolemember 'db_owner' , 'xxxx99'
> I'm still getting the same error.
> Now since this SPROC is being called from another SPROC which is called fr
om
> cfml page, I'm wondering if it's a coldfusion problem. The reason I say th
is
> is because logged in as a WIN NT domain user, Query Analyzer runs this
> statement without errors.
> Thoughts?
> TIA,
> Eric
> "Tibor Karaszi" wrote:
>