Tuesday, October 7, 2014

MSSQL Database/Server User/Login Mapping

This is just a quick post, mainly so that I can refer back to it when I forget the correct syntax.

In Microsoft SQL server there two separate principals. There is a principal (user) at the database level and there is a principal (login) at the server level. A login is mapped to one or more users in one or more databases. A password or an windows account is associated to the login. When you move a database or restore from one server to another, this mapping breaks and must restore it. People all too often drop the user and login and then recreated them both. If you have permissions associated to the user though, you'll have to recreate those. The best thing to do is just update the mapping (which just updates the associated SIDs in the appropriate system tables.)

USE [DatabaseName]
ALTER USER [UserAccount] WITH LOGIN = [LoginAccount]

It's simple, but since I'm not always in DB land, I find myself forgetting the syntax.

No comments:

Post a Comment