/*

This script is inspired by two MS KB articles:

"PRB: 'Troubleshooting Orphaned Users' Topic in Books Online is Incomplete"

http://support.microsoft.com/kb/274188

and

"HOW TO: Resolve Permission Issues When You Move a Database Between Servers That Are Running SQL Server"

http://support.microsoft.com/kb/240872

These articles remind us that there is difference between a "login" (defined at the server level) and a "user" (defined at the database level). The login name and the user name are often the same and this can lull us into the assumption that that a login and a user are the same.

A login and a user are linked by a server-specific, security identifier (SID). When a database is moved to a new server (or removed and restored to the same server) this link can be undefined (or broken). This script attempts to define/repair this link.

*/ --Server level: USE master IF NOT EXISTS(SELECT * FROM sysxlogins WHERE name = 'myLogin') BEGIN EXECUTE sp_addlogin @loginame='myLogin',@passwd='mYp1wd',@defdb='myDb' END

-- Database level: USE myDb

IF NOT EXISTS(SELECT * FROM sysusers WHERE name = 'myLogin') BEGIN EXECUTE sp_grantdbaccess 'myLogin' EXECUTE sp_addrolemember 'db_datareader','myLogin' EXECUTE sp_addrolemember 'db_datawriter','myLogin' EXECUTE sp_addrolemember 'dbWebSolutionsUsers','myLogin' END BEGIN EXECUTE sp_change_users_login 'update_one','myLogin','myLogin' END