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,682 other followers

Archive for December 25th, 2012

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: Read the rest of this entry »

Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »

 
%d bloggers like this: