Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Database inaccessible with SQL Authentication

Posted on 2011-05-10
10
Medium Priority
?
257 Views
Last Modified: 2012-05-11
We had a server crash.  When the replacement was staged, we moved the SQL DB files (.mdf and ldf) to the appropriate SQL Server folder.

Connection via Windows Authentication works fine.  The DB is visible as well as all of its tables.

However, when connecting via SQL Authentication, the connection is made and the DB shows in the list of databases.  But when expanding the database to see the tables, the error:
"Database <db name> is not accessible. (Microsoft.SQLServer.Express.ObjectExplorer)"

I have checked all settings against other sites (each sites has its own SQL setup), and everything looks correct.
0
Comment
Question by:mdreed
  • 5
  • 4
10 Comments
 
LVL 9

Expert Comment

by:radcaesar
ID: 35731426
The database doesn't have access for this user. Add this user names under Security --> Users (In SSMS). Login through Windows authentication with sufficient rights.
0
 

Author Comment

by:mdreed
ID: 35731469
The user is listed correctly under <db name> -> Security -> Users

Anything else to check?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35740204
Did you checked the Security at the SQL Server level AND at the Database level?
Make sure that at the server level is you right click on the SQL login you are after you can see the box checked besides that database name under Properties- User Mapping section.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:mdreed
ID: 35745991
When I check the database name under User mapping, I get the error:
"User, group, or role '<login name>' already exists in the current database.
(Error: 15023)
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 35746791
That means you should go to the Security tab under THAT database and remove the login we are talking about then go back at the Security Server level and check that box again it must work.
Same can be done through SQL statements
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35746850
You could run the commands below to revoke/add back the user to the database in question:

USE your_db_name
GO
--revoke the user access
if exists (select * from sysusers where name = 'your_login_name')
exec sp_revokedbaccess 'your_login_name'
--add user access to the database
if not exists (select * from sysusers where name = 'your_login_name')
exec sp_grantdbaccess 'your_login_name'

0
 

Author Comment

by:mdreed
ID: 35747001
When I deleted the User and then checked the database box under Security/Logins the User reappeared but without read write privileges, so the database tables did not appear.  When I added the read write priveleges all of the tables appeared.

Since the User was recreated under the database section,  why did I have to delete it ?  It appears to have solved the problem (sort of) except for the read write priveleges issue, but I am still confused.

What we are trying to do is to recover data from a database on a dead server.  To do this we are:
1. Copying the .mdf .ldf files from the defective server.
2. Running our normal SQL scripts for all new servers which creates the database, user, and login.
3. Copy the saved .mdf .ldf to the new server.
Then, Windows authentication works, but SQL Authentication does not.

Is there a step(s) misssing?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35747288
Does the new server has Mixed mode enabled? Please check that by right click server name select Properties and check under Security to make sure it was installed with "SQL Server and Windows Authentication mode"
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35747359
Other things to worry about is access and rights as you could see the logins are at the SERVER and DATABASE level and rights granted to the objects in your db for those logins.

You could use standard database roles db_datareader db_datawriter to grant access for the login if you did not saved the acual permissions from your old crashed box but I suggest you do not go beyond that unless your app does not work properly for that login.

And you should have a maintenance/backup/restore plan that would save you a lots of headaces in the future in case of a crash.

http://download.microsoft.com/download/7/b/1/7b17fc16-444f-4ccd-af90-edb378da8831/hp_whitepaper_sqlL.pdf.
0
 

Author Closing Comment

by:mdreed
ID: 35747527
Seems to work now with your help.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question