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 Friday, February 19, 2010 1:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:29 AM
Points: 1,384, Visits: 401
No the databases exists and all have a normal status as does each of the databases that returned errors.

Mark
Post #869395
Posted Friday, February 19, 2010 1:33 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
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
Posted Friday, February 19, 2010 2:15 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:29 AM
Points: 1,384, Visits: 401
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
Posted Friday, February 19, 2010 2:20 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
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
Posted Friday, February 19, 2010 3:00 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:16 PM
Points: 990, Visits: 2,223
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
Posted Friday, February 19, 2010 4:57 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
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
Posted Monday, February 22, 2010 10:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:29 AM
Points: 1,384, Visits: 401
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
Posted Monday, February 22, 2010 1:44 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
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
Post #870707
Posted Thursday, May 27, 2010 2:58 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:54 AM
Points: 701, Visits: 126
Great script, Thanks!
Post #929317
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse