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
 General SQL Server Forums
 New to SQL Server Programming
 Accessing Another Database in Same Server

Author  Topic 

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-13 : 05:31:16
I have two Databases ( DevelopDB and TestDB ) with different login users (Eg: devUserLogin and testUserLogin ) in same Server

While accessing table of TestDB from DevelopDB am getting following error..

USE DevelopDB
GO
SELECT * FROM TestDB.dbo.TableName

Msg 916, Level 14, State 1, Line 1
The server principal "devUserLogin" is not able to access the database "TestDB" under the current security context.

Please let me know the solution....



--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 05:35:43
check the role to which login devUserLogin is mapped onto the database TestDB

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-13 : 05:45:59
Database Role Membership for devUserLogin is db_owner

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 05:50:11
you mean in testdb?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-13 : 05:55:49
[code]
In testDB i have only testUserLogin user whereas two users testUserLogin, devUserLogin are there in DevelopDB...

i.e.
DatabaseName Users (Under Security Tab --> Users)
-------------- --------------------
TestDB testUserLogin
DevelopDB devUserLogin, testUserLogin


I can able to access both databases from TestDB, but not from DevelopDB[/code]

--
Chandu
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-02-13 : 05:59:35
Unless devUserLogin is part of the sysAdmin group, that login won't be able to access TestDB as there is no specific login on that database for it.
You'll either need to create the login in TestDB or make devUserLogin an SA.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 06:10:12
quote:
Originally posted by bandi


In testDB i have only testUserLogin user whereas two users testUserLogin, devUserLogin are there in DevelopDB...

i.e.
DatabaseName Users (Under Security Tab --> Users)
-------------- --------------------
TestDB testUserLogin
DevelopDB devUserLogin, testUserLogin


I can able to access both databases from TestDB, but not from DevelopDB


--
Chandu


then how do you think you will be able to access tables in testdb
for that your devsqllogin has to be mapped to one of roles for testdb

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 06:11:43
Also it would be better to give devsqllogin only required permissions as mapping to higher role might give it access to restricted objects/actions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-13 : 06:14:56
quote:
for that your devsqllogin has to be mapped to one of roles for testdb

How to do that? Am not good at administration...


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 06:20:17
quote:
Originally posted by bandi

quote:
for that your devsqllogin has to be mapped to one of roles for testdb

How to do that? Am not good at administration...


--
Chandu


script out and apply to testdb

or use transfer database objects task in ssis

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -