SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Accessing Another Database in Same Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 02/13/2013 :  05:31:16  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/13/2013 :  05:35:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 02/13/2013 :  05:45:59  Show Profile  Reply with Quote
Database Role Membership for devUserLogin is db_owner

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/13/2013 :  05:50:11  Show Profile  Reply with Quote
you mean in testdb?

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

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 02/13/2013 :  05:55:49  Show Profile  Reply with Quote

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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 02/13/2013 :  05:59:35  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/13/2013 :  06:10:12  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/13/2013 :  06:11:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 02/13/2013 :  06:14:56  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/13/2013 :  06:20:17  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000