How to return the remote database name which is calling a stored proc in a different database?

Question

I have a number of different databases on a single SQL Server 2008 R2. For arguments sake, let's call them DB_A, DB_B, and DB_C. I've been asked to develop as stored proc that will live on DB_A. This stored proc will be used to drop and create indexes, and also store some extra information about the index in a table on DB_A. When this stored proc is called from DB_C or DB_C, it will be able to drop and create indexes on the calling database, but store the extra information about the index in the table on DB_A.

Here's what I would like to do: I would like the stored proc to be able to get the name of the calling database WITHOUT having to request the database name as a parameter.

Here is a simple example:

USE [DB_A]

CREATE PROC sp_WhatDatabaseAmICallingFrom
AS 
BEGIN
      DECLARE @calling_db NVARCHAR(128)
      SET @calling_db = DB_NAME()
      PRINT 'calling database: ' + @calling_db
END

When I execute the stored procedure in DB_A ...

EXEC sp_WhatDatabaseAmICallingFrom

...it returns: "calling database: DB_A"

When I execute the stored procedure in DB_B ...

USE DB_B
GO

EXEC DB_A.dbo.sp_WhatDatabaseAmICallingFrom

...it returns: "calling database: DB_A".

After reading up on various SQL Server Metadata Functions, this is exactly what it should do. But what I would like is to change the code so that it sets @calling_db to the name of calling database, so that my example stored proc would print: "calling database: DB_B".

Unfortunately, I can't find any Metadata Functions that can do this. Any ideas on how this can be done?

Answer 1

Building off of Mike's answer, this is an example of a solution that works in Sql2012.

You must create the function while logged in as a user with sufficient permissions ('VIEW SERVER STATE'), but those who use the function only need permission to execute the function itself.

USE [DB_A];
GO

CREATE FUNCTION dbo.GetCallingDbCatName()
RETURNS nvarchar(128) 
WITH EXECUTE AS SELF
AS
BEGIN
    DECLARE @result nvarchar(128);
    SELECT TOP 1 @result = DB_NAME(resource_database_id) 
        FROM sys.dm_tran_locks 
        WHERE request_session_id = @@SPID 
            AND resource_type = 'DATABASE' 
            AND request_owner_type = 'SHARED_TRANSACTION_WORKSPACE' 
        ORDER BY IIF(resource_database_id != DB_ID(), 0, 1);
    RETURN @result;
END
GO

USE [DB_A];
SELECT DB_A.dbo.GetCallingDbCatName(); --"DB_A"
USE [DB_B];
SELECT DB_A.dbo.GetCallingDbCatName(); --"DB_B"
USE [DB_C];
SELECT DB_A.dbo.GetCallingDbCatName(); --"DB_C"

And from there, you should be able to build the actual functionality you need without needing to pollute the master dbcat.

EDIT 2019-07-26 - FYI: This works because every connection to (or use of) a dbcat other than master or tempdb will have a SHARED_TRANSACTION_WORKSPACE lock. The explanation below is from this page ( And another page that explains it similarly )

The purpose of the SHARED_TRANSACTION_WORKSPACE lock owner is to prevent SQL Server from acquiring EXCLUSIVE_TRANSACTION_WORKSPACE locks, that is, to prevent a process from dropping, restoring, or changing readability status for a database, while the database is in use. The reason SQL Server does not acquire these locks for the master and tempdb databases is that these databases cannot be dropped, or have their readability status changed. In addition, we never restore tempdb, and to restore the master database, we must start the entire server in single-user mode so, again, SHARED_TRANSACTION_WORKSPACE locks are unnecessary.

So when you call the function that lives in DB_A from DB_B, that query returns 2 rows; one for the use of DB_B where the query is running, and one for the use of DB_A where the function runs/lives. This also means that the above query will not work for more complicated call-stacks (DB_A calls func in DB_B which then calls func in DB_C which tries to determine the caller's dbcat; the ORDER BY won't be able to properly choose between DB_A and DB_B.)

Finally, this solution also works from a CLR method. You must remember to execute the "wrapper" Sql function with sufficient permission to use db_tran_locks, and the SqlFunction attribute must have "SystemDataAccess = SystemDataAccessKind.Read". The "context connection" uses the same SPID as the caller, and it lives in the dbcat where the assembly lives (and consequently where the wrapper-function/sproc lives that exposes the CLR method.)

For reference, here's my test CLR method:

[SqlFunction(IsDeterministic = false, DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read), SqlMethod(OnNullCall = true)]
public static SqlString GetCallingDbcatName()
{
    string sqlResult = "";
    using (var connection = new System.Data.SqlClient.SqlConnection("context connection=true"))
    {
        connection.Open();
        var sqlCmd = connection.CreateCommand();
        sqlCmd.CommandText = @"
SELECT TOP 1 DB_NAME(resource_database_id) 
  FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
    AND resource_type = 'DATABASE'
    AND request_owner_type = 'SHARED_TRANSACTION_WORKSPACE'
  ORDER BY IIF(resource_database_id != DB_ID(), 0, 1); ";
        sqlResult = sqlCmd.ExecuteScalar().ToString();
    }
    return sqlResult;
}

Answer 2

I know this thread is quite old but I found something that at least helped me.

If you are using DB_A and are calling an stored procedure in DB_B like:

USE DB_A
EXEC db_b.dbo.sproc

The only way I have found to get the "calling database" id is to run a select against sys.dm_tran_locks inside the stored procedure. request_session_id should be your spid, resource_type should be DATABASE and request_owner_type should be SHARED_TRANSACTION_WORKSPACE. Such a shared lock always exist on a database for each connected session. The query would be something like:

SELECT resource_database_id FROM sys.dm_tran_locks WHERE request_session_id = @@SPID and resource_type = 'DATABASE' and request_owner_type = 'SHARED_TRANSACTION_WORKSPACE'

Although this requires the executing user to have at least VIEW SERVER STATE permissions on the server. In my case that was not a problem though..

Answer 3

To make SP run in the context of current connection you need create your SP on master database and make it a system object.

USE MASTER 
GO 

CREATE PROC sp_WhatDatabaseAmICallingFrom
AS 
BEGIN
      DECLARE @calling_db NVARCHAR(128)
      SET @calling_db = DB_NAME()
      PRINT 'calling database: ' + @calling_db
END
GO

EXEC sp_ms_marksystemobject 'sp_WhatDatabaseAmICallingFrom'
GO

Check how it works:

USE [DB_A]
GO

EXEC sp_WhatDatabaseAmICallingFrom
GO