Fix users for SQL 7 thru 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mike Tutor » Fix users for SQL 7 thru 2008

29 posts, Page 3 of 3

«««1 2 3

Fix users for SQL 7 thru 2008

Rate Topic

Display Mode

Topic Options

Author

Message

Mark D Powell

Posted Friday, February 19, 2010 1:24 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:26 PM
Points: 1,258, Visits: 341

No the databases exists and all have a normal status as does each of the databases that returned errors.

Mark

Post #869395

Mike Tutor

Posted Friday, February 19, 2010 1:33 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 22, 2013 1:30 PM
Points: 298, Visits: 87

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')

Post #869402

Mark D Powell

Posted Friday, February 19, 2010 2:15 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:26 PM
Points: 1,258, Visits: 341

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 --

Post #869434

Mike Tutor

Posted Friday, February 19, 2010 2:20 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 22, 2013 1:30 PM
Points: 298, Visits: 87

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.

Post #869441

rVadim

Posted Friday, February 19, 2010 3:00 PM

SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 19, 2013 1:36 PM
Points: 887, Visits: 2,062

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

Post #869466

Mike Tutor

Posted Friday, February 19, 2010 4:57 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 22, 2013 1:30 PM
Points: 298, Visits: 87

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

Post #869515

Mark D Powell

Posted Monday, February 22, 2010 10:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:26 PM
Points: 1,258, Visits: 341

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

Post #870564

Mike Tutor

Posted Monday, February 22, 2010 1:44 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 22, 2013 1:30 PM
Points: 298, Visits: 87

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. BigGrin

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

Post #870707

farside41

Posted Thursday, May 27, 2010 2:58 PM

Say Hey Kid

Group: General Forum Members
Last Login: Thursday, February 14, 2013 9:51 AM
Points: 696, Visits: 123

Great script, Thanks!

Post #929317

« Prev Topic | Next Topic »

29 posts, Page 3 of 3

«««1 2 3

Permissions