Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Fix users for SQL 7 thru 2008 Expand / Collapse
Author
Message
Posted Monday, February 8, 2010 11:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
Yes I have seen that. What is happening is the procedure is at the point where it is either trying to fix a lost or orphaned user and it is attempting to do it the SQL 2000 way, which is to update the system tables directly.

If you look at the line right after the PRINT statement that reads either
'PRINT '        Fixing Lost User    :    ' + @name' OR 'PRINT '        Removing Orphan User:    ' + @name'

If you have my latest version of the script, it should read


'IF @SvrLevel NOT IN ('90','10')'

Checking to see if this is prior to SQL 2005/2008. If not, then please recopy the script since I have made several changes to the script.

If this is what you have and it is not working, e-mail me at mtutor@gmail.com
Post #861935
Posted Monday, February 8, 2010 1:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
D'oh! One more fix. The line:

IF @SvrLevel NOT IN ('90','10')

Should be:

IF @SvrLevel NOT IN ('9.','10')

I will fix and submit.

:)
Post #862021
Posted Friday, February 12, 2010 11:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
I have found through my own experience that removing users that own objects will cause problems... like you can't get to the objects anymore. I fixed this in the latest version. The script doesn't remove them anymore but it notifies you of how many objects they own.

This will be available as soon as they publish my update. :)
Post #864807
Posted Wednesday, February 17, 2010 1:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
Last update. It now has defaults on all parameters. Also you can tell it to change ownership on objects owned by orphaned users.

Enjoy.
Post #867466
Posted Wednesday, February 17, 2010 2:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
This really should be in SQL 2010 :) Nicely done!!


Post #867496
Posted Friday, February 19, 2010 6:56 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:50 PM
Points: 95, Visits: 219
I'm getting an error with the script that doesn't make sense to me: Msg 208, Level 16, State 1, Procedure sp_fix_user_logins, Line 25
Invalid object name 'Master..SysDatabases'.

Any idea why I would get this?
Post #869043
Posted Friday, February 19, 2010 9:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
Hmmm... I am not sure why you can't see master.dbo.sysdatases. The table does exist although in 2005 and 2008 they hide it and you use mater.sys.databases (a view) instead. Accessing the view is the preferred methodology anyway, but I need the script to work on SQL 2000 as well, and the view does not exist there. I assume you do not enough permissions to see the actual table.

Solution: If you are not concerned about utilizing this stored procedure for a SQL 2000 or earlier database, then change 'Master..SysDatabases' and 'master.dbo.sysdatasbes' (sorry I am not consistent) to 'master.sys.databases' and it will work fine for SQL 2005 and 2008. You will likely have the same problem with sysusers, sysobjects, and syslogins, all of which are accessed by this stored procedure.

There are others on this web site that are better than I am who may be able to tell you why you can't see the system tables. If you post a question on one of the forums and get an answer, please let us know in this thread what you find out.

Mike.
Post #869185
Posted Friday, February 19, 2010 12:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:00 PM
Points: 1,384, Visits: 402
ON 2000 any idea what would cause some of the database checks to fail with:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'ARIS7.dbo.SysUsers'.
Msg 16916, Level 16, State 1, Procedure usp_fix_user_logins, Line 162
A cursor with the name 'SysUsers_Cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure usp_fix_user_logins, Line 183
A cursor with the name 'SysUsers_Cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure usp_fix_user_logins, Line 304
A cursor with the name 'SysUsers_Cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure usp_fix_user_logins, Line 305
A cursor with the name 'SysUsers_Cursor' does not exist.

I got errors like the above on five out of 100 databases on this instance.

Mark D Powell
Post #869369
Posted Friday, February 19, 2010 1:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
Not sure, just looking at the errors it would appear as though it may be that the problem stems from the fact that I didn't put brackets ([ and ]) around database and table names. I am lazy that way sometimes. :/ I don't know for sure if that is the problem since I am not seeing it, but I should put the brackets in anyway. The subsequent cursor errors I believe will disappear once this problem is fixed.

I will change the script to include brackets everywhere. :)
Post #869384
Posted Friday, February 19, 2010 1:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
Looking into this further... forget what I just said. The problem appears to me as though the issue is that there are entries in your master.dbo.sysdatabases table that are invalid. In other words, the database 'ARIS7' that shows up in this error message exists in your master.dbo.sysdatabases, but does not actually exist on your system. Is this the case? What is happening is the cursor is not being defined because the table 'ARIS7.dbo.SysUsers' is not being found. That's all I can figure without actually looking at your system.

Let me know what you find out.
Post #869394
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse