[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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