April 12, 2004 at 4:31 am
I have a SQL Server 2000 instance running sp3 (in a cluster).
When I create a NEW SQL user, and give that user access to ONE database (public and DBO) I cannot list ANY databases from Enterprise Manager when I login with that user. If I refresh the Enterprise Manager Databases View, eventually it will display the Databases - but only after about 10 minutes and I get access violation errors in my SQL and NT logs.
I tried to recreate this problem on another Clustered SQL Server 2000 Instance - this one running sp3a, and I am NOT able to recreate this problem.
I can't imagine that this is a Service Pack issue. Does anyone have any suggestions?
April 12, 2004 at 8:11 am
Check that you have the GUEST account properly setup. I had a similiar issue with ODBC connctors and found that someone (possibly me :-}) had deleted the guest account. There is a KB article on it.
KlK
April 12, 2004 at 8:45 am
THANK YOU!!! I found the KB article and I think I have the guest account set up properly. It has public access to the master and tempdb DBs - but no others because it is not defined in the model DB.
It is so strange. everytime I disconnect and connect again, I get 'no items' in the DB list via enterprise manager, but if I refresh, I get the list - AND THE ERRORS!
April 13, 2004 at 4:24 am
Thank you Thank you!!! I looked more closely at that KB article
http://support.microsoft.com/default.aspx?scid=kb;en-us;315523&Product=sql2k
about the GUEST account having been deleted directly from sysusers in a Database.
and I checked this out by doing a select * from sysusers on each of my Databases and 'some' of them had the guest account deleted directly from their sysusers table!!!
Turns out that the switch in the server setttings that allows modifications to be made directly to system catalogs was turned ON on one of my older Instances and this older instance is the one that I had been migrating databases from - thus moving the databases - as well as the problem - to my new system.
To test the script in the article, I took a 'healthy' DEV Instance, turned ON the switch in the server setttings that allows modifications to be made directly to system catalogs, then I deleted the guest account in one of my user databases. This 'broke' it and i could no longer get a list of DBs in EM without sysadmin priviledges. I ran the script which checked each DB and adds the guest account make in if it find it missing and then disables the "allow modifications to be made directly to system catalogs" switch. AND THIS WORKED!!!!
April 13, 2004 at 7:33 am
Glad to help. I drove me crazy for awhile until I figured it out.
KlK
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply