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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.