How to Fix Login Failed for User in Mssql
Often you will encounter this error if you are restoring a database from one machine into another. What causes the error: On a full backup o...
https://www.czetsuyatech.com/2021/07/mssql-login-failed-for-user.html
Often you will encounter this error if you are restoring a database from one machine into another.
What causes the error:
On a full backup of the database, it includes all the objects within it like tables, views, procedures, etc and also the users.
So when we restore the database on another machine, and the user is not on that machine. Or if it is there, the mapping would be deleted.
What to do:
1.) Execute this:
exec sp_revokedbaccess 'username'
http://msdn.microsoft.com/en-us/library/ms188909.aspx
This would delete the carried user in the database
2.) Now open your MSSQL Server Managerment Studio
3.) Click Security->Logins
4.) Right click the "user" click Properties, and in the "User Mapping" tab click the appropriate rights that we want the user to have for the database chosen
What causes the error:
On a full backup of the database, it includes all the objects within it like tables, views, procedures, etc and also the users.
So when we restore the database on another machine, and the user is not on that machine. Or if it is there, the mapping would be deleted.
What to do:
1.) Execute this:
exec sp_revokedbaccess 'username'
http://msdn.microsoft.com/en-us/library/ms188909.aspx
This would delete the carried user in the database
2.) Now open your MSSQL Server Managerment Studio
3.) Click Security->Logins
4.) Right click the "user" click Properties, and in the "User Mapping" tab click the appropriate rights that we want the user to have for the database chosen
1 comment
Thanks a lot. I and another colleague of mine spent a lot of time figuring out this issue but finally resolved it with your solution.
Post a Comment