quote:

---

Originally posted by James K

Grant the necessary permissions to the MultiDB_ConnectUser on the objects in DB2 that are referred by your stored proc in DB1.

Normally, when if you grant permission to a principal to execute a stored procedure, you don't need to specifically grant permissions to objects called by the stored procedure. For example, if the stored procedure selects data from a table, the principal does not need to be explicitly granted the select permission on that table. This type of "ownership chaining" does not work across databases. So you either need to explicitly grant permissions to the objects in the remote database (DB2 in your case), or you need to enable "cross database ownership chaining". The latter has some additional security risks and considerations, so I would recommend the former.

---

SELECT  sp.name AS LOGINNAME
FROM    sys.server_principals sp
        JOIN sys.database_principals dp ON ( sp.sid = dp.sid )
WHERE   dp.name = 'MultiDB_ConnectUser'

quote:

---

Originally posted by James K

Try couple of things: First, run this query against DB1 and then DB2 and see if the LOGINNAME returned is the same.

SELECT  sp.name AS LOGINNAME
FROM    sys.server_principals sp
        JOIN sys.database_principals dp ON ( sp.sid = dp.sid )
WHERE   dp.name = 'MultiDB_ConnectUser'

Second, open up the code for the stored procedure and see if the stored procedure has an EXECUTE as clause immediately following the create statement, or somewhere in the code. If there is, test whether that user/login has privileges on DB2.

If neither of that is the problem, see if you can execute the statement that queries the table in DB2 from a query window with DB1 as the current database.

---

quote:

---

Originally posted by arkiboys

quote:

---

Originally posted by James K

Try couple of things: First, run this query against DB1 and then DB2 and see if the LOGINNAME returned is the same.

SELECT  sp.name AS LOGINNAME
FROM    sys.server_principals sp
        JOIN sys.database_principals dp ON ( sp.sid = dp.sid )
WHERE   dp.name = 'MultiDB_ConnectUser'

Second, open up the code for the stored procedure and see if the stored procedure has an EXECUTE as clause immediately following the create statement, or somewhere in the code. If there is, test whether that user/login has privileges on DB2.

If neither of that is the problem, see if you can execute the statement that queries the table in DB2 from a query window with DB1 as the current database.

---

---

sp_change_users_login @Action='Report';

sp_change_users_login @Action='update_one', @UserNamePattern='MultiDB_ConnectUser', 
   @LoginName='MultiDB_ConnectUser';

quote:

---

Originally posted by James K

If you restored the DB1 from another server, this can happen. In SQL Server, a "LOGIN" is a server level principal and "USER" is a database level principal. A user can be associated with a login. If the USER in DB1 and USER in DB2 are not associated with the same login, then SQL Server will not execute queries that you send to DB2 from DB1 in the same security context as that of the MultiDB_ConnectUser in DB2.

Assuming both these users should be associated with the same login, run this command on DB1.

sp_change_users_login @Action='Report';

I am expecting that it will show a row with MultiDB_ConnectUser. Assuming you do, to associate that user with the login MultiDB_ConnectUser, run this command

sp_change_users_login @Action='update_one', @UserNamePattern='MultiDB_ConnectUser', 
   @LoginName='MultiDB_ConnectUser';

There are more details on this page http://msdn.microsoft.com/en-us/library/ms175475.aspx

---

quote:

---

Originally posted by James K

If you are running RTM, you will need to update to SP1. See this connect issue: https://connect.microsoft.com/SQLServer/feedback/details/354291/the-server-principal-is-not-able-to-access-the-database-under-the-current-security-context-microsoft-sql-server-error-916

If you have SP1 or later, I can't think of a reason other than the ones that we have been through already.

---

USE DB1
GO
SELECT SID FROM sys.database_principals WHERE name = 'MultiDB_ConnectUser'
GO
USE DB2
GO
SELECT SID FROM sys.database_principals WHERE name = 'MultiDB_ConnectUser'
GO
USE master
GO
SELECT SID FROM sys.server_principals  WHERE name = 'MultiDB_ConnectUser'
GO

quote:

---

Originally posted by James K

I was thinking the server is SQL 2008 because you posted the question to the SQL 2008 forum. I know we have covered this earlier, but can you verify that all 3 queries below returns the same SID?

USE DB1
GO
SELECT SID FROM sys.database_principals WHERE name = 'MultiDB_ConnectUser'
GO
USE DB2
GO
SELECT SID FROM sys.database_principals WHERE name = 'MultiDB_ConnectUser'
GO
USE master
GO
SELECT SID FROM sys.server_principals  WHERE name = 'MultiDB_ConnectUser'
GO

---

USE master
GO

-- change context to the MultiDB_ConnectUser login
EXECUTE AS LOGIN = 'MultiDB_ConnectUser';
GO

-- check if the login has access to the databases
SELECT HAS_PERMS_BY_NAME('DB1','DATABASE','ANY');
SELECT HAS_PERMS_BY_NAME('DB2','DATABASE','ANY');
GO

REVERT
GO

-- change to DB2
USE DB2
GO

-- switch context
EXECUTE AS USER = 'MultiDB_ConnectUser';
GO
-- check if there is select permission on the table.
SELECT HAS_PERMS_BY_NAME('dbo.YourTableNameHere','OBJECT','SELECT');
GO

REVERT
GO

quote:

---

Originally posted by James K

I don't know which piece of the puzzle is missing. You have to go through each database and test if the login has access to those databases, and then in each database test whether the user has required permissions. Here is an example of how you can do this. You would need to modify it to match your environment.

USE master
GO

-- change context to the MultiDB_ConnectUser login
EXECUTE AS LOGIN = 'MultiDB_ConnectUser';
GO

-- check if the login has access to the databases
SELECT HAS_PERMS_BY_NAME('DB1','DATABASE','ANY');
SELECT HAS_PERMS_BY_NAME('DB2','DATABASE','ANY');
GO

REVERT
GO

-- change to DB2
USE DB2
GO

-- switch context
EXECUTE AS USER = 'MultiDB_ConnectUser';
GO
-- check if there is select permission on the table.
SELECT HAS_PERMS_BY_NAME('dbo.YourTableNameHere','OBJECT','SELECT');
GO

REVERT
GO

---

quote:

---

Originally posted by James K

What I showed was only an example. You have to examine each object you are querying to see if it has the required permissions. If that does not yield any clues, start with a simple example and work your way through to see where it breaks.

---