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 »
Like this:
Like Loading...
