Q
Problem solve Get help with specific problems with your technologies, process and projects.

Users can't connect after database reintalled

I have a SQL database in my server. I made a back up before reinstalling the server. Now it has so many tables created by users other than dbo. But after restoring the database, no users can access their data. Only the dbo (sa) can access the data, even though the owner for the user tables are they themselves. I have granted all rights to all the users, but even after that, the users can't connect to the database. In the Enterprise Manager, the users under the database are only showing the sa; it is not listing any one of others. If try creating the users, it is shows this user already exists for the database. If I try adding the users, the same thing happens. I dropped the users and recreated them -- still not working. Can you please give a solution, as well as kindly give an explanation to my query?

Logins to your SQL Server get an ID assigned to them. When you add a user to a database, it gets an ID inside that database. This database ID is mapped to the login ID. When you reinstalled the SQL Server and created new logins, they received new IDs. Data restored in a database does not change which means the IDs you have in the database are now not matching up. When you add a user, the name is checked for uniqueness. They do exist in the database, you just can not see them. Enterprise Manager is simply a GUI application that issues queries against your database to display information. This is why they are not showing in Enterprise Manager. Issue an sp_change_users_login for each of the users and they will be properly mapped. This will allow access and display them in Enterprise Manager.

I would strongly suggest that you eliminate the practice of having objects owned by a user other than sa.

 

For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close