• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Create an Orphaned User

This may sound like a crazy request, but I need to create an orphaned user for testing purposes, however I'd really like to do it using just one machine.

My thinking was to backup my database, then delete and recreate a user, then restore the database.  Turns out, that doesn't cut it.  Anyone else have any ideas?

This needs to work with SQL 2000 and 2005.

Thanks!
0
Cayman21
Asked:
Cayman21
  • 2
1 Solution
 
RiteshShahCommented:
Guest user is a kind of orphan user, can't you use it?
0
 
RiteshShahCommented:
to create orphaned user.

CREATE LOGIN [UserA] WITH PASSWORD=N'UserA',
         DEFAULT_DATABASE=[AdventureWorks],
         CHECK_EXPIRATION=OFF,
         CHECK_POLICY=OFF

CREATE USER [UserA] FOR LOGIN [UserA]
ALTER USER [UserA] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember N'db_datareader', N'UserA'
EXEC sp_addrolemember N'db_datawriter', N'UserA'
GO


DROP LOGIN [UserA]


now, UserA user will be orphaned user. BTW, following article could be useful to you.

http://www.sql-server-performance.com/articles/dba/Fixing_Orphaned_Users_in_SQL_Server_Database_p1.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now