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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 connect to another database

Author  Topic 

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-09-08 : 06:21:19
Hi guys,
In a stored proc in database i.e. DB1 I am also reading data from another database i.e. DB2
But I get this error:
Cannot execute as the database principal because the principal "MultiDB_ConnectUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Note that the user MultiDB_ConnectUser does indeed exist.
Any suggestions please?
Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-08 : 09:35:22
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.
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-09-08 : 11:19:55
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.


The user MultiDB_ConnectUser has select, alter, delete, update permission on table tbl1 in DB2 which is referred to in the sp which is in DB1
Still the same error.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-08 : 11:44:17
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.
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-09-08 : 12:40:34
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.

Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-09-08 : 12:43:16
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.




In DB1 it does not exist whereas it exists in DB2
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-08 : 13:12:46
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
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-09-09 : 03:47:48
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



Hi,
I did what you said as follows:
use DB1
go
sp_change_users_login @Action='Report';
This returned one row for
'MultiDB_ConnectUser'

Then ran the following:
sp_change_users_login @Action='update_one', @UserNamePattern='MultiDB_ConnectUser',
@LoginName='MultiDB_ConnectUser';

Now I run a sp from DB1 which reads data from DB2
Now this time the error message is different which is:
The server principal "MultiDB_ConnectUser" is not able to access the database "DB2" under the current security context.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-09 : 08:24:43
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.
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-09-09 : 12:48:53
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.


Hi, I run @@version and shows:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

In this sql server 2012 (Dev server), we have restored the DB1 and DB2 from the production server which is sql server 2008
and could the whole issue be the way the MultiDB_CurrentUser is created in sql which I ran initially to create the login?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-09 : 13:18:37
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
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-09-10 : 04:11:11
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



Yes they all return as below:
0x4362BAC18080564CB9877090A4A94B0A
0x4362BAC18080564CB9877090A4A94B0A
0x4362BAC18080564CB9877090A4A94B0A
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-10 : 08:09:41
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
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-09-11 : 05:59:55
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



Hi,
They all return 1
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-11 : 09:42:23
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.
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-09-11 : 09:58:57
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.


what do you mean by:
"start with a simple example and work your way through to see where it breaks."
Thank you
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-11 : 10:19:15
What I meant is,
1) log into the server as the MultiDB_ConnectUser in SSMS
2) change the database to DB1 in a query window.
3) write a select statement against a table in DB2 that you are interested in and see if that works.
4) if that works create a stored procedure in DB1 (while logged in as another user perhaps) that selects from that table and grant execute permission on that stored proc to MultiDB_ConnectUser.
5) See if the MultiDB_ConnectUser can execute that stored proc from DB1.

You can also examine the stored procedure and find out which specific statement is failing.
Go to Top of Page
   

- Advertisement -