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.Expre
I have checked all settings against other sites (each sites has its own SQL setup), and everything looks correct.
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.Expre
I have checked all settings against other sites (each sites has its own SQL setup), and everything looks correct.
radcaesar
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.
ASKER
The user is listed correctly under <db name> -> Security -> Users
Anything else to check?
Anything else to check?
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.
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.
ASKER
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)
"User, group, or role '<login name>' already exists in the current database.
(Error: 15023)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
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'
ASKER
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?
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?
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"
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
http://download.microsoft.com/download/7/b/1/7b17fc16-444f-4ccd-af90-edb378da8831/hp_whitepaper_sqlL.pdf.
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
http://download.microsoft.com/download/7/b/1/7b17fc16-444f-4ccd-af90-edb378da8831/hp_whitepaper_sqlL.pdf.
ASKER
Seems to work now with your help.