SQL Clone
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
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1844 Visits: 463
No the databases exists and all have a normal status as does each of the databases that returned errors.

Mark
Mike Tutor
Mike Tutor
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

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

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

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

I hope you enjoy the script otherwise.
rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

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

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

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

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

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
farside41
farside41
SSC Eights!
SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)

Group: General Forum Members
Points: 889 Visits: 219
Great script, Thanks!
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