Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fix users for SQL 7 thru 2008


Fix users for SQL 7 thru 2008

Author
Message
Mike Tutor
Mike Tutor
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 101
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
Mike Tutor
Mike Tutor
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 101
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.

Smile
Mike Tutor
Mike Tutor
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 101
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. Smile
Mike Tutor
Mike Tutor
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 101
Last update. It now has defaults on all parameters. Also you can tell it to change ownership on objects owned by orphaned users.

Enjoy.
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
This really should be in SQL 2010 Smile Nicely done!!



Brett Phipps
Brett Phipps
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 230
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?
Mike Tutor
Mike Tutor
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 101
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.
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1672 Visits: 460
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
Mike Tutor
Mike Tutor
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 101
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. Smile
Mike Tutor
Mike Tutor
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 101
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search