The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,880 other followers

Re-adding a user to SQL Server 2000 and up when it was Orphaned

Posted by jpluimers on 2012/12/25

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:

  1. sp_dropuser – drops a user from the current database
  2. sp_droplogin – drops a login from the current server
  3. sp_addlogin – adds a login to the database server
  4. sp_adduser – adds a user to the current database (you can add both a SQL user – with name and password – and a Windows user)
  5. 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:

Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'MyUser' does not exist in the current database.
The login 'MyUser' does not exist.

Those errors are no problem.

Otherwise the results will be like this:

User has been dropped from current database.
Login dropped.
New login created.
Granted database access to 'MyUser'.
'MyUser' added to role 'db_datareader'.
'MyUser' added to role 'db_datawriter'.

–jeroen

via: Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008 « The Wiert Corner – irregular stream of Wiert stuff.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: