In SQL Server 2000 and up, the easiest way to re-add a user that got orphaned is with a script like below.
The script does not correct the SID, but in stead sets new permissions (in this case, db_datareader and db_datawriter). Most of the times that is not a problem.
The script uses these stored procedures:
- sp_dropuser – drops a user from the current database
- sp_droplogin – drops a login from the current server
- sp_addlogin – adds a login to the database server
- sp_adduser – adds a user to the current database (you can add both a SQL user – with name and password – and a Windows user)
- sp_addrolemember – adds a member to a certain role
More modern versions have alternatives to these stored procedures, but the stored procedures work with the widest ranges of SQL Server versions.
-- Execute this script as SA or DB Administrator use MyDatabase -- the databae where you want the user to be re-added to -- you cannot perform a 'use [MyDatabase]' without destroying the context (and declared variables) -- http://stackoverflow.com/questions/9165513/sql-server-change-current-database-via-variable -- it is possible with exec (@exec_stmt) (as sp_droplogin does it), but it is a bit cumbersome for a relatively simple script declare @loginname sysname declare @passwd sysname set @loginname = 'MyUser' set @passwd = 'MyPassword' -- from current database exec sp_dropuser @loginname -- from server exec sp_droplogin @loginname -- to current server exec sp_addlogin @loginname, @passwd -- to current database exec sp_adduser @loginname -- roles to add the user to exec sp_addrolemember db_datareader, @loginname exec sp_addrolemember db_datawriter, @loginname
If the user didn’t exist in the database, or didn’t exist as a login on the server, you can get two errors like these: Read the rest of this entry »
The Wiert Corner - irregular stream of stuff 