Fix users for SQL 7 thru 2008

,

Usage:

EXEC sp_fix_user_logins @DBName = <DataBase>, @Action = <ActionOption>, @User = <User Specification>

sp_fix_user_logins:
- Will work on SQL 7 thru SQL 2008 versions of SQL Server.
- Either reports or fixes lost and or orphaned users in the current database.
- Can report/fix users on one or all databases.
- Will NOT fix READ ONLY and Offline databases, and it will NOT remove Orphans that own oblects.
- Can automatically change object ownership if @ForceFix is set to 'FixLogin'
- Will change object ownership to dbo if FixLogin is indicated and no @Owner is provided

Orphan user: A user in the current database that has no corresponding login in master.
Lost user: A user in the current database that has a login in master but the sid is out of sync.

<DataBase> = The name of any valid user defined database, or '*' for all'

<ActionOption> =
Report or ReportAll: Displays a list of all lost or orphaned users
ReportLost: Displays a list of all lost users
ReportOrphans: Displays a list of all orphaned users
Fix or FixAll: Repairs all lost users (changes the sid) and removes all orphaned users that do not own objects.
FixLost: Repairs all lost users (changes the sid).
FixOrphans: Removes all orphaned users that do not own objects.

<User Specification> =
NULL or '*': All users
Anyhting else: A specific user only or you may use LIKE operators

<ForceFix> =
NULL
: Do Not change object ownership for orphaned users
FixLogin : If an orphaned user is to be removed and owns objects, then change ownership of objects owned

<Owner> =
NULL
: Change object ownership to dbo
<username> : If the orphaned user owns objects then change ownership of objects owned to this user

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_fix_user_logins]
 @DBName VARCHAR(100)='*', 
 @Action VARCHAR(20)='Report', 
 @User VARCHAR(100)='*',
 @ForceFix VARCHAR(8)='NULL',
 @Owner VARCHAR(50)='dbo'
AS 
 BEGIN
 ------------------------------------------------------
 -- Stored Procedure dbo.sp_fix_user_logins
 -- Created by Mike Tutor
 -- Feb. 22, 2010
 -- Works for SQL Server 7 to 2008
 ------------------------------------------------------
 DECLARE @err_flag SMALLINT
 SET @err_flag = 0
 IF @DBName IS NULL SET @DBName = '*'
 IF @User IS NULL SET @User = '*'

 SET @DBName = LTRIM(RTRIM(@DBName))
 SET @Action = LTRIM(RTRIM(@Action))
 SET @User = LTRIM(RTRIM(@User))
 
 IF @DBName NOT IN (SELECT name from [master].[dbo].[sysdatabases] WHERE name NOT IN ('master','msdb','model','tempdb'))
 BEGIN
 IF @DBName NOT IN ('?' ,'*')
 BEGIN
 PRINT '****'
 PRINT 'ERROR:' + @DBName + ' is an invalid database name. It must be one of the user databases on this server or ''*'',''?''.'
 PRINT '****'
 PRINT ''
 PRINT ''
 SET @err_flag = 1
 END
 ELSE IF @DBName != '*'
 SET @err_flag = 1
 END
 IF @Action NOT IN ('Report','ReportAll','ReportLost','ReportOrphans','Fix','FixAll','FixLost','FixOrphans')
 BEGIN
 IF @Action != '?'
 BEGIN
 PRINT '****'
 PRINT 'ERROR:' + @Action + ' is an invalid Action request. Valid Actions requests are:'
 PRINT ' ''Report(All)'',''ReportLost'',''ReportOrphans'',''Fix(All)'',''FixLost'',''FixOrphans'',''?'''
 PRINT '****'
 PRINT ''
 PRINT ''
 END
 SET @err_flag = 1
 END
 IF @ForceFix NOT IN ('NULL','FixLogin')
 BEGIN
 PRINT '****'
 PRINT 'ERROR:' + @ForceFix + ' is an invalid @ForceFix option. Valid options are:'
 PRINT ' NULL (blank) AND ''FixLogin'''
 PRINT '****'
 PRINT ''
 PRINT ''
 SET @err_flag = 1
 END
 IF @err_flag = 1
 BEGIN
 PRINT '------------------------------------------------------------------------------------------------------------'
 PRINT 'Usage: EXEC sp_fix_user_logins @DBName = <DataBase>, @Action = <ActionOption>, @User = <User Specification>'
 PRINT '------------------------------------------------------------------------------------------------------------'
 PRINT 'sp_fix_user_logins:'
 PRINT ' - Will work on SQL 7 thru SQL 2008 versions of SQL Server.'
 PRINT ' - Either reports or fixes lost and or orphaned users in the current database.'
 PRINT ' - Can report/fix users on one or all databases.'
 PRINT ' - Will NOT fix READ ONLY databases, and it will NOT remove Orphans that own oblects.'
 PRINT ' - Can automatically change object ownership if @ForceFix is set to ''FixLogin'''
 PRINT ' - Will change object ownership to dbo if FixLogin is indicated and no @Owner is provided'
 PRINT ''
 PRINT 'Orphan user: A user in the current database that has no corresponding login in master.'
 PRINT 'Lost user: A user in the current database taht has a login in master but the sid is out of sync.'
 PRINT ''
 PRINT '<DataBase> = The name of any valid user defined database, or ''*'' for all'
 PRINT ''
 PRINT '<ActionOption> ='
 PRINT ' NULL,Report,ReportAll : Displays a list of all lost or orphaned users '
 PRINT ' ReportLost : Displays a list of all lost users '
 PRINT ' ReportOrphans : Displays a list of all orphaned users '
 PRINT ' Fix or FixAll : Repairs all lost users (changes the sid) and removes all orphaned users'
 PRINT ' FixLost : Repairs all lost users (changes the sid).'
 PRINT ' FixOrphans : Removes all orphaned users that do not own objects.'
 PRINT ''
 PRINT '<User Specification> ='
 PRINT ' NULL or ''*'' : All users'
 PRINT ' Anyhting else : A specific user only or you may use LIKE operators'
 PRINT ''
 PRINT '<ForceFix> ='
 PRINT ' NULL : Do Not change object ownership for orphaned users'
 PRINT ' FixLogin : If an orphaned user is to be removed and owns objects, then change ownership of objects owned'
 PRINT ''
 PRINT '<Owner> ='
 PRINT ' NULL : Change object ownership to dbo'
 PRINT ' <username> : If the orphaned user owns objects then change ownership of objects owned to this user'
 PRINT '------------------------------------------------------------------------------------------------------------'
 END
 ELSE
 BEGIN
 SET NOCOUNT ON
 DECLARE
 @UserName VARCHAR(200),
 @ObjName VARCHAR(200),
 @DBReadOnly INT,
 @DBOffline INT,
 @DBSuspect INT,
 @DBInRecovery INT,
 @DBInLoad INT,
 @DBNotRecovered INT,
 @DBCantBeFixed INT,
 @DBCantBeRead INT,
 @sid VARBINARY(85),
 @uid SMALLINT,
 @isntuser INT,
 @login_sid VARBINARY(85),
 @Database VARCHAR(100),
 @SvrLevel VARCHAR(2),
 @str NVARCHAR(1000),
 @objectsOwned SMALLINT,
 @ParmDefinition NVARCHAR(50),
 @ObjOwnerUID SMALLINT

 SET @SvrLevel = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(2))
 IF @DBName = '*'
 BEGIN
 SET @str =
 'DECLARE SysDatabases_Cursor CURSOR FOR
 SELECT name FROM [master].[dbo].[sysdatabases] WHERE name NOT IN (''master'',''msdb'',''model'',''tempdb'')'
 EXEC sp_executesql @str
 OPEN SysDatabases_Cursor
 END
 ELSE
 BEGIN
 SET @str =
 'DECLARE SysDatabases_Cursor CURSOR FOR
 SELECT name FROM [master].[dbo].[sysdatabases] WHERE name = ' + '''' + @DBName + ''''
 EXEC sp_executesql @str
 OPEN SysDatabases_Cursor
 END

 WHILE (1=1)
 BEGIN
 SET @Database = NULL
 SET @DBReadOnly = NULL
 SET @DBOffline = NULL
 SET @DBSuspect = NULL
 SET @DBInRecovery = NULL
 SET @DBInLoad = NULL
 SET @DBNotRecovered = NULL

 FETCH NEXT FROM SysDatabases_Cursor INTO @Database
 IF (@@FETCH_STATUS = -1) BREAK

 SELECT @DBReadOnly = DATABASEPROPERTY(@Database,'isReadOnly')
 SELECT @DBOffline = DATABASEPROPERTY(@Database,'isOffline')
 SELECT @DBSuspect = DATABASEPROPERTY(@Database,'isSuspect')
 SELECT @DBInRecovery = DATABASEPROPERTY(@Database,'isInRecovery')
 SELECT @DBInLoad = DATABASEPROPERTY(@Database,'isInLoad')
 SELECT @DBNotRecovered = DATABASEPROPERTY(@Database,'isNotRecovered')
 SET @str = 'Database = ' + @Database + ' '
 SET @DBCantBeRead = ISNULL(@DBOffline,0) + ISNULL(@DBSuspect,0) + ISNULL(@DBInRecovery,0) + ISNULL(@DBInLoad,0) + ISNULL(@DBNotRecovered,0)
 SET @DBCantBeFixed = ISNULL(@DBCantBeFixed,0) + ISNULL(@DBReadOnly,0)
 IF @DBCantBeFixed > 0
 BEGIN
 IF @DBReadOnly = 1 SET @str = @str + '(Read Only) '
 IF @DBOffline = 1 SET @str = @str + '(Offline) '
 IF @DBSuspect = 1 SET @str = @str + '(Suspect) '
 IF @DBInRecovery = 1 SET @str = @str + '(In Recovery) '
 IF @DBInLoad = 1 SET @str = @str + '(In Load) '
 IF @DBNotRecovered = 1 SET @str = @str + '(Not Recovered) '
 IF LEFT(@Action,3) = 'Fix' SET @str = @str + '- NOT Fixed'
 END
 PRINT @str
 PRINT REPLICATE('-',LEN(@str))

 IF @DBCantBeRead = 0
 BEGIN
 IF @User = '*'
 BEGIN
 SET @str =
 'DECLARE SysUsers_Cursor CURSOR FOR
 SELECT name, sid, uid, isntuser FROM [' + @Database + '].[dbo].[sysusers] WHERE islogin = 1
 AND name NOT IN (''dbo'', ''guest'',''sys'',''INFORMATION_SCHEMA'',''system_function_schema'')'
 EXEC sp_executesql @str
 OPEN SysUsers_Cursor
 END
 ELSE
 BEGIN
 SET @str =
 'DECLARE SysUsers_Cursor CURSOR FOR
 SELECT name, sid, uid, isntuser FROM [' + @Database + '].[dbo].[sysusers] WHERE islogin = 1 AND name LIKE ' + '''' + @User + '''' +
 ' AND name NOT IN (''dbo'', ''guest'',''sys'',''INFORMATION_SCHEMA'',''system_function_schema'')'
 EXEC sp_executesql @str
 OPEN SysUsers_Cursor
 END

 WHILE (1=1)
 BEGIN
 SET @UserName = NULL
 SET @sid = NULL
 SET @uid = NULL
 SET @login_sid = NULL
 SET @isntuser = NULL
 SET @objectsOwned = NULL

 FETCH NEXT FROM SysUsers_Cursor INTO @UserName, @sid, @uid, @isntuser
 IF (@@FETCH_STATUS = -1) BREAK

 IF @Action IN ('Report','ReportAll','ReportLost') OR (ISNULL(@DBReadOnly,0) = 1 AND LEFT(@Action,3) = 'Fix')
 BEGIN
 IF (SELECT COUNT(*) FROM [master].[dbo].[syslogins] WHERE name = @UserName AND isntuser = @isntuser) = 1 
 IF (SELECT COUNT(*) FROM [master].[dbo].[syslogins] WHERE name = @UserName AND sid != @sid) = 1
 PRINT ' Lost User : ' + @UserName
 END
 IF @Action IN ('Report','ReportAll','ReportOrphans') OR (ISNULL(@DBReadOnly,0) = 1 AND LEFT(@Action,3) = 'Fix')
 BEGIN
 IF (SELECT COUNT(*) FROM [master].[dbo].[syslogins] WHERE name = @UserName AND isntuser = @isntuser) = 0
 BEGIN
 SET @ParmDefinition = N'@objectsOwned_out SMALLINT OUTPUT';
 SET @str =
 'SET @objectsOwned_out = (SELECT COUNT(*) FROM [' + @Database + '].[dbo].[sysobjects] WHERE uid = '
 + CAST(@uid AS VARCHAR(5)) + ' AND xtype = ''U'')'
 EXEC sp_executesql @str, @ParmDefinition, @objectsOwned_out = @objectsOwned OUTPUT
 IF @objectsOwned > 0 
 SET @str = ' Objects owned = ' + CAST(@objectsOwned AS VARCHAR(5))
 ELSE
 SET @str = ' '
 PRINT ' Orphan User : ' + @UserName + @str
 END
 END
 IF @Action IN ('Fix','FixAll','FixLost') AND ISNULL(@DBReadOnly,0) != 1
 BEGIN
 IF (SELECT COUNT(*) FROM [master].[dbo].[syslogins] WHERE name = @UserName AND isntuser = @isntuser) = 1
 BEGIN
 SELECT @login_sid = sid FROM [master].[dbo].[syslogins] WHERE name = @UserName
 IF @sid != @login_sid
 BEGIN
 PRINT ' Fixing Lost User : ' + @UserName
 IF @SvrLevel NOT IN ('9.','10') -- IF NOT SQL 2005 or 2008)
 BEGIN
 SET @ParmDefinition = N'@login_sid_in VARBINARY(85)';
 SET @str =
 'UPDATE [' + @Database + '].[dbo].[sysusers] SET sid = @login_sid_in WHERE name = ' + '''' + @UserName + ''''
 EXEC sp_executesql @str, @ParmDefinition, @login_sid_in = @login_sid
 END
 ELSE
 BEGIN
 SET @str =
 'USE [' + @Database + '];'+ CHAR(13) + CHAR(10) +
 'ALTER USER ' + @UserName + ' WITH LOGIN = ' + @UserName + ';' + CHAR(13) + CHAR(10)
 EXEC sp_executesql @str
 END
 END
 END
 END
 IF @Action IN ('Fix','FixAll','FixOrphans') AND ISNULL(@DBReadOnly,0) != 1
 BEGIN
 IF (SELECT COUNT(*) FROM [master].[dbo].[syslogins] WHERE name = @UserName AND isntuser = @isntuser) = 0
 BEGIN
 SET @ParmDefinition = N'@objectsOwned_out SMALLINT OUTPUT';
 SET @str =
 'SET @objectsOwned_out = (SELECT COUNT(*) FROM [' + @Database + '].[dbo].[sysobjects] WHERE uid = '
 + CAST(@uid AS VARCHAR(5)) + ' AND xtype = ''U'')'
 EXEC sp_executesql @str, @ParmDefinition, @objectsOwned_out = @objectsOwned OUTPUT
 IF @objectsOwned > 0 AND @ForceFix = 'Fixlogin'
 BEGIN
 SET @ObjOwnerUID = NULL
 SET @ParmDefinition = N'@ObjOwnerUID_out SMALLINT OUTPUT';
 SET @str =
 'SELECT @ObjOwnerUID_out = uid FROM [' + @Database + '].[dbo].[sysusers] WHERE name = '''
 + @Owner +''''
 EXEC sp_executesql @str, @ParmDefinition, @ObjOwnerUID_out = @ObjOwnerUID OUTPUT
 IF @ObjOwnerUID IS NULL
 PRINT ' **ERROR: User ' + @owner + ' is not a valid user name in ' + @Database
 ELSE
 BEGIN
 SET @str =
 'DECLARE SysObjects_Cursor CURSOR FOR
 SELECT name FROM [' + @Database + '].[dbo].[sysobjects] WHERE uid = '
 + CAST(@uid AS VARCHAR(5)) + ' AND xtype = ''U'''
 EXEC sp_executesql @str
 OPEN SysObjects_Cursor
 WHILE (1=1)
 BEGIN
 SET @ObjName = NULL
 FETCH NEXT FROM SysObjects_Cursor INTO @ObjName
 IF (@@FETCH_STATUS = -1) BREAK
 SET @ParmDefinition = N'@objectsOwned_out SMALLINT OUTPUT';
 SET @str =
 'SET @objectsOwned_out = (SELECT COUNT(*) FROM [' + @Database + '].[dbo].[sysobjects] WHERE uid = '''
 + CAST(@ObjOwnerUID AS VARCHAR(5)) + '''' + ' AND name = ''' + @ObjName + '''' + 'AND xtype = ''U'')'
 EXEC sp_executesql @str, @ParmDefinition, @objectsOwned_out = @objectsOwned OUTPUT
 IF @objectsOwned > 0
 BEGIN
 PRINT ' **ERROR: User ' + @Owner + ' already owns an object named ' + @ObjName + '. Cannot change object owner.'
 END
 ELSE
 BEGIN
 SET @str =
 'USE [' + @Database + '];'+ CHAR(13) + CHAR(10) +
 'EXEC sp_changeobjectowner ''[' + @UserName + '].[' + @ObjName + ']'', ' + '''' + @owner + ''';' + CHAR(13) + CHAR(10)
 EXEC sp_executesql @str
 END
 END
 CLOSE SysObjects_Cursor
 DEALLOCATE SysObjects_Cursor
 END -- IF @ObjOwnerUID IS NULL
 SET @ParmDefinition = N'@objectsOwned_out SMALLINT OUTPUT';
 SET @str =
 'SET @objectsOwned_out = (SELECT COUNT(*) FROM [' + @Database + '].[dbo].[sysobjects] WHERE uid = '
 + CAST(@uid AS VARCHAR(5)) + ' AND xtype = ''U'')'
 EXEC sp_executesql @str, @ParmDefinition, @objectsOwned_out = @objectsOwned OUTPUT
 END -- IF @objectsOwned > 0 AND @ForceFix = 'Fixlogin'
 IF @objectsOwned = 0
 BEGIN
 PRINT ' Removing Orphan User: ' + @UserName
 SET @str =
 'USE [' + @Database + '];' + CHAR(13) + CHAR(10) +
 'EXEC dbo.sp_revokedbaccess N' + '''' + @UserName + '''' + ';' + CHAR(13) + CHAR(10)
 EXEC sp_executesql @str
 END
 ELSE
 PRINT ' Removing Orphan User: ' + @UserName + ' Objects owned = ' + CAST(@objectsOwned AS VARCHAR(5)) + ' NOT removed'
 END
 END
 END -- While (1=1) SysUsers_Cursor loop
 CLOSE SysUsers_Cursor
 DEALLOCATE SysUsers_Cursor
 END -- IF @@DBCantBeRead = 0
 END -- While (1=1) SysDatabases_Cursor loop
 CLOSE SysDatabases_Cursor
 DEALLOCATE SysDatabases_Cursor
 END -- @err_flag = 1 ELSE
 END -- dbo.sp_fix_user_logins

Rate

4.43 (7)

Share

Share

Rate

4.43 (7)