Database inaccessible with SQL Authentication

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.
mdreedAsked:
Who is Participating?
 
lcohanDatabase AnalystCommented:
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
 
radcaesarCommented:
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
 
mdreedAuthor Commented:
The user is listed correctly under <db name> -> Security -> Users

Anything else to check?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
lcohanDatabase AnalystCommented:
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
 
mdreedAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
mdreedAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
lcohanDatabase AnalystCommented:
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
 
mdreedAuthor Commented:
Seems to work now with your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.