Just read a blog post illustrating the SQL account problem when moving or restoring SQL database between servers. As the blog says:
“SQL Server security is a little complex, but for our purposes it’s enough to consider two core artifacts: logins and users. Logins are instance-level objects (stored in master) and users are database-level objects (stored in the user database). Each of these are responsible for authorization in their respective domains, i.e., used to grant permissions at the instance and at the database respectively. But only logins are used for authentication. So in order to even log on to the server you have to have a login.
Users and logins are associated with each other through a matching identifier called a SID, and in order for a person to connect to and use a database, he must have a user in the target database and matching login on the instance. And here’s where our problem comes from: while users are stored in and move with the database, logins are not and do not. They’re left behind:
The result of this is that someone who could connect to D when it lived on S may no longer be able to do so when it moves to T because their login is missing. This is reparable, of course: you just need to manually add the required logins to the new instance. It’s also not easily automatable, since the target instance may already have a different login with the same name, which would cause a collision.”
It sounds the Contained Database in the next version of SQL Server “Denali” solved the authentication problem. For now, we can resync the user login by using
sp_change_users_login 'update_one', 'username', 'username'
No comments:
Post a Comment