We recently moved all our databases on a new server (what a pain) and we felt on a common issue. But that issue dosen't seems to have a specific solution.
All the users, (and all the applications using these users) fail to connect, or fail to query the database.
WE CAN SEE THESE USERS in SQL management studio, they're here, in the security file at the bottom, just like everything is normal.
If i delete one of those users, then recreate it, with all the same parameters, it suddently work.
I'm aware of security, login, users rights, i have an idea of what owned schema is, but not more.
But it seems like there is something more, or there is something i don't see correctly. Something that binds the user to a specific sql server, and this something isin't updated when you move a database onto a new server.
What is this thing ? Any idea ? Any idea how to apply the right value on it ?
2条答案
按热度按时间bmvo0sr51#
As noted in the comments, server level Security\Logins need to map to database level Security\Users. I've seen scripts in the past provided by MSDN pages that help you script out logins when migrating from one SQL Server instance to another. Right now it sounds like you have "orphaned users", and might check out this page: https://msdn.microsoft.com/en-us/library/ms175475(v=sql.110).aspx
That might offer some help to clean them up. I'm not familiar with the exact commands, but you should also be able to script the security mapping of users to databases, too, even more so if you still have access to the old server.
xzlaal3s2#
Note, when you create a new instance of SQL Server, it will default to Windows Authentication only. Then if you use the Database Migration Assistant tool, all the users will be created properly -- but you'll still be unable to log in with SQL Server Authentication until you turn that on: https://www.stellarinfo.com/blog/how-to-fix-sql-server-database-error-18456/#:~:text=enable%20sql%20authentication