July 3, 2008 at 7:43 pm
Good night everybody,
It matters that there was a problem connecting the users to the users database, so it was neccesary to access to the master database and the sysxlogins table to delete manually all the database users, checking previously in the server properties in the server settings the option ALLOW MODIFICATIONS TO BE MADE DIRECTLY TO THE SYSTEM CATALOGS, so all the logins were deleted from the sysxlogins table in the master database, later when we tried to access again to the database there was a message that said that we were not able to access to the database because of the sa password, in appearance it was very probably that the register of the sa login was deleted too, because we can't connect to sql server again.
So in the case that the sa login was deleted, how could it be possibly to restore it?....
July 4, 2008 at 12:14 am
Do you have a backup of the master database from before the deletions?
Deleting directly from sysxlogins is a little (no, actually it's very) extreme and there probably is another way. In general, modifying the system tables is asking for trouble.
it was neccesary to access to the master database and the sysxlogins table to delete manually all the database users
sysxlogins doesn't hold database users. It holds the SQL logins. The database users are in sysusers
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2008 at 8:46 am
Good morning!,
Definitively it's a big mistake deleting registers in the system tables, but it's a long story about our system... but, I'm writing now because I found a solution, in this case the big advantage was that it was a NEW INSTALLATION IN A TESTING ENVIRONMENT, so when we installed the SQL Server 2000, we made a copy of the complete folder where master, model, msdb and tempdb databases are located or lodged, and when we had the problem, we renamed the present or actual folder and we replaced it with the folder we had saved previously during the installation.
When we did this we could get connected to the SQL with the SA login, of course it was neccesary to make attach to the users databases, but the problem was solved.
Thanks a lot, I hope it can help anytime, 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply