0

Problem: SQL Server account can browse but not open DB

Background: After our TFS Server upgrade from 2010 to 2015 and SQL 2008r2 to 2014, we can't get an existing SQL account to connect to the database. Server authentication has been set to "SQL Server and Windows Auth mode"

The account 'myweb' is allowed in the "public" settings under the SQL Server roles and 'myweb' can open a SQL management session and see the DBs. However it cant open the DB or view properties.

"The database xxx is not accessible"

Under the DB it exists as a user and has the db_datareader role. No joy. As a trial I gave 'myweb' super powers db_owner etc but no change in behaviour.

Any idea what is stopping an SQL account from accessing the DB?

Improve this question
3
  • Was the database moved or backed up/restored? The server login and database user user might be orphaned (disconnected). Can any other use get into the database? Can you confirm the database is actually functioning (i.e. not in a restoring state). Can you add a new user that has access? Commented Aug 25, 2016 at 6:38
  • open myweb under DB node and check its user type. Does it have a login? Commented Aug 25, 2016 at 6:47
  • Other domain\accounts have no problem accessing DBs. Problem solved: In checking in the login type I found an artefact of the old server (even though drop and add server used) under "securables" I deleted the user and created a fresh new one and I am in !! I see where dean was on the money. Thanks Commented Aug 25, 2016 at 6:58

1 Answer 1

Reset to default
2

The user is orphaned (ie, the same-named login and the database user have different SIDs). Use this to sync them again:

USE db; GO ALTER USER myweb WITH LOGIN = myweb; GO
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.