February 19, 2010 at 6:56 am
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?
February 19, 2010 at 9:19 am
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.
February 19, 2010 at 1:00 pm
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
February 19, 2010 at 1:09 pm
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. 🙂
February 19, 2010 at 1:21 pm
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.
February 19, 2010 at 1:24 pm
No the databases exists and all have a normal status as does each of the databases that returned errors.
Mark
February 19, 2010 at 1:33 pm
Can you execute the following code with no errors logged in with the same user you are getting the errors with?
USE [master]
GO
SELECT name, sid, uid, isntuser FROM [ARIS7].[dbo].[SysUsers] WHERE islogin = 1
AND name NOT IN ('dbo', 'guest','sys','INFORMATION_SCHEMA','system_function_schema')
February 19, 2010 at 2:15 pm
The statement fails but the table exists within the database. I tried the statement there wtihin the database and it also failed. So I changed SysUsers to sysusers and the statement works.
Each of the five failing databases are vendor products created with _CS in the collating sequence.
I went in and changed only the table_name in the cursor code two or three times and the resulting procedure ran cleanly for all databases.
HTH -- Mark D Powell --
February 19, 2010 at 2:20 pm
That's cool. Thanks for debugging that. I don't have any databases with case sensitivity in place so I didn't foresee that problem.
I will fix the script so that they are are all sysdatabases, syslogins, sysusers, sysobjects. 🙂
I hope you enjoy the script otherwise.
February 19, 2010 at 3:00 pm
Nice script - Thank you.
One more problem: how to exclude OffLine databases? I've added
AND ISNULL(version, 0) > 0
and it seem to work with 2005 but not sure about other SQL Server versions.
SELECT * from Master..SysDatabases
WHERE name NOT IN ('master','msdb','model','tempdb') AND ISNULL(version, 0) > 0
--Vadim R.
February 19, 2010 at 4:57 pm
Thanks for the suggestion rVadim. I changed the script to handle Offline databases as well, although I did it a little differently than you did. Rather than skipping them in the select statement, I used DATABASEPROPERTY(@Database,'isOffline') instead and then didn't process it but reported the database as being offline.
- Mike
February 22, 2010 at 10:59 am
Yes, your script is very nice. I see someone has suggested handling offline database. I think offline, read only, and suspect need to be handled.
Have you updated the posted source? If so, I think I will want to pull down a new copy since I have very poor T-SQL skills at present.
Thanks.
Mark
February 22, 2010 at 1:44 pm
Yes, I have updated the source numerous times, but per your suggestion the script now handles many different database states including read only, offline, suspect, in recovery, in load, not recovered. I have not been fully tested the script on any states other than read only and offline since (thankfully) I do not have any databases in any of the other states and I do not have the time or inclination to intentionally get one of my databases into any of those states. 😀
You may need to wait a day to download the script again as it takes awhile for the script updates to show up on the web site.
Mike
May 27, 2010 at 2:58 pm
Great script, Thanks!
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply