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 
