Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Fix Orphaned Database Users All SQL Server Versions

From Wiki

Jump to: navigation, search

Alter the database to the one you need to fix any orphaned database users. Note: This script does not handle database users that are mapped to logins that do not have the exact same login as the database user. There will be an update in the form of a blog that will show how to take advantage of your DBA database and a maintained table for security that deploys logins and database users that are not the same names. i.e. Login BillBob to Database User JoeBob

  1. SET NOCOUNT ON
  2. USE AdventureWorks
  3. GO
  4. DECLARE @loop INT
  5. DECLARE @USER sysname
  6. DECLARE @sqlcmd NVARCHAR(500) = ''
  7.  
  8. IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL
  9.  BEGIN
  10.   DROP TABLE #orphaned
  11.  END
  12.  
  13. CREATE TABLE #Orphaned (UserName sysname,IDENT INT IDENTITY(1,1))
  14.  
  15. INSERT INTO #Orphaned (UserName)
  16. SELECT [name] FROM sys.database_principals WHERE [type] IN ('U','S') AND is_fixed_role = 0 AND [Name] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')
  17.  
  18. IF(SELECT COUNT(*) FROM #Orphaned) > 0
  19. BEGIN
  20.  SET @loop = 1
  21.  WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned)
  22.   BEGIN
  23.     SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
  24.     IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
  25.      BEGIN
  26.         IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER')
  27.          BEGIN
  28.             SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] FROM WINDOWS'
  29.             Exec(@sqlcmd)
  30.             PRINT @sqlcmd
  31.          END
  32.         IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER')
  33.          BEGIN
  34.             SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] WITH PASSWORD = N''password'''
  35.             Exec(@sqlcmd)
  36.             PRINT @sqlcmd
  37.          END
  38.      END
  39.      
  40.     SET @sqlcmd = 'ALTER USER [' + @USER + '] WITH LOGIN = [' + @USER + ']'
  41.     Exec(@sqlcmd)
  42.     PRINT @USER + ' link to DB user reset';
  43.     SET @loop = @loop + 1
  44.   END
  45. END
  46. SET NOCOUNT OFF

--onpnt 00:30, 12 December 2011 (GMT)

831 Rating: 3.1/5 (26 votes cast)