December 6, 2009 at 8:15 am
I will accept the verbal thrashing and public ridicule that this kind of a bone-head move deserves ... ONLY IF YOU PROVIDE PRODUCTIVE INSIGHT, otherwise it's just hot air and the only reason it hasn't happened to you is 'cause you're lucky ... (defensive? who me?:-) )
Environment
OS : Windows 7 Home Premium
SQL Server : SQL Server Express installed with Visual studio 2008
This is a home system from which I work on personal Classic ASP and ASP.NET apps. I use the IIS 7.5 that came with Windows 7, and have installed the SQL Management Studio from the SQL Server 2005 Discs. I also have installed VS.NET 2003, VS.NET 2008.
This is a new install, which I am attempting to transfer a database from an older (Win XP) machine over to.
Scenario
I scripted the source database from my XP box. Apparently I had chosen the "Script DROP Statements" option. In the resulting script file it included the drop (and create) statements for all the system logins.
The original machine name was "PALADIN" ... so there was a entry to drop the login ... "PALADIN\JEFF" where "JEFF" is the same username on BOTH machines.
Not yet registering the relevence of dropping the logins, I did a find/replace on ALL OCCURENCES of "Paladin" with "Jester"; the name of the new machine ... (can you see it coming?)
I logged onto my new machine ("Jester") under my username "Jeff" which was given "Administrator" priveledges under Windows 7.
I then opened up Microsoft SQL Server Management Studio, connected to the instance JESTER\SQLEXPRESS and opened up the specific database I am looking to execute the script against.
I execute the script and everything after the DROP statements fails ... "no permission to perform that function" ...
Situation
In case you haven't figured it out yet ... I DROPPED the SQL login "JESTER\JEFF" from the database, which was assigned a sysadmin role (it installed that way).
The SA account is disabled and I don't know the password for it anyway (it was assigned some default password when installed by VS.NET).
I have ONE login which is assigned a "PUBLIC" role in Server Roles.
Problem
I cannot enable the SA account, nor do I know the login so that does not help me.
I cannot add the JESTER\JEFF account back to the LOGINS as I cannot logged in with an account with enough permissions to perform the action.
Basically I am unable to log in with an account that has enough permissions to perform any of the fixes I have discovered online.
My only choice, it seems is to uninstall and re-install Visual Studio .NET 2008 entirely. I had hoped that there would be at least an option on the install media to reinstall just SQL Server Express ... but I cannot find even that.
Question
Before I reinstall VS.NET ... is there any other way I can possibly create an sysadmin account in SQL Server Express given this circumstance. While I have to admit this is a pretty stupid thing to have done ... I got BIG money that says I am not the only one who has done it.
Is there another solution?
Thank You,
G
December 6, 2009 at 8:19 am
Is the BUILTIN\Administrator login still there ? If so, and you are an admin, then you should be able to create new accounts and assign permissions.
December 6, 2009 at 9:50 am
homebrew01 (12/6/2009)
Is the BUILTIN\Administrator login still there ? If so, and you are an admin, then you should be able to create new accounts and assign permissions.
Thank you for your quick response! Regarding the BUILTIN accounts ... I would have to assume that none of them are there anymore ... Here is the relevant fragment of the actual code that I ran ...
[font="Courier New"]/****** Object: Login [BUILTIN\Administrators] Script Date: 12/04/2009 17:39:07 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTIN\Administrators')
DROP LOGIN [BUILTIN\Administrators]
GO
/****** Object: Login [BUILTIN\Users] Script Date: 12/04/2009 17:39:07 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTIN\Users')
DROP LOGIN [BUILTIN\Users]
GO
/****** Object: Login [NT AUTHORITY\SYSTEM] Script Date: 12/04/2009 17:39:07 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITY\SYSTEM')
DROP LOGIN [NT AUTHORITY\SYSTEM]
GO
/****** Object: Login [JESTER\Jeff] Script Date: 12/04/2009 17:39:07 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'JESTER\Jeff')
DROP LOGIN [JESTER\Jeff][/font]
God ... the more I look at this the more stupid I was ... I deserve this misery ... I AM Surprised however that you would be allowed to delete these accounts.
If I can get the SA account enabled and logged in I should be able recreate all these accounts though right?
December 6, 2009 at 10:01 am
Are your databases backed up ?? You might have to re-install, or maybe you can recover them from your MASTER db backup ? Hold out for someone more knowledgeable to reply.
December 6, 2009 at 10:13 am
homebrew01 (12/6/2009)
Are your databases backed up ?? You might have to re-install, or maybe you can recover them from your MASTER db backup ? Hold out for someone more knowledgeable to reply.
Naww ... this was a fresh install and I hadn't even built any databases yet. It didn't even occur to me to backup the master db so soon after installing. I will next time though!!
Hopefully I'm not completely screwed ... I won't be losing any data though so it's not too big of a deal I guess if I have to re-install. The only problem I have then is, if SQLEXPRESS was installed with VS.NET 2008 ... and the VS.NET uninstall/fix does not have an option for uninstalling just SQL Server Express ... do I have to uninstall the ENTIRE development environment to re-install the SQL Server Express components! Maybe that's a question for another forum though.
December 6, 2009 at 11:35 am
Did you try this to get sa account?
December 6, 2009 at 4:40 pm
jymoorthy-1100764 (12/6/2009)
http://support.microsoft.com/kb/322336%5B/quote%5D
Yes ... the problem is that the SA is disabled and I don't know the password anyway. I think it was assigned a random password when it was installed. I don't recall assigning a password to it during installation anyway. I don't think I have an account, any longer, with permissions enough to enable the SA account and then reset the password.
December 6, 2009 at 6:09 pm
So what is the error message exactly that you are getting when you try and access the instance with the sa account? It could be that you are still in Windows Authentication mode and not Mixed mode, which you will not be able to access the instance with sa in that case. If you did not specify an sa password during installation then by default it is NULL until it is set.
It sounds like you need to enable Mixed mode security. The KB article previously posted has steps on how to check for this and change it further on down the article.
Joie Andrew
"Since 1982"
December 6, 2009 at 11:27 pm
Joie Andrew (12/6/2009)
So what is the error message exactly that you are getting when you try and access the instance with the sa account? It could be that you are still in Windows Authentication mode and not Mixed mode, which you will not be able to access the instance with sa in that case. If you did not specify an sa password during installation then by default it is NULL until it is set.It sounds like you need to enable Mixed mode security. The KB article previously posted has steps on how to check for this and change it further on down the article.
Nope ... the database was/is definitely set up to be used in mixed mode. I am unable to login with the SA account because I don't know the password for it. I get a "login failed for user 'sa'" message when I attempt to login with any password, incl. a blank password.
The reason why I can't login with the BUILTIN accounts (I think) is because they were inadvertantly dropped from the database (see script fragment from previous post).
The question is (well one of them anyway) ... is there a way I can re-establish those logins without having to re-install SQL Server and not being able to login using the SA account? It seems like there should be a utility or something that I could run that woudl re-establish the accounts that are installed when the software was first installed.
Thanks
G
December 7, 2009 at 12:47 am
If you haven't created any database objects such as user databases or logins yet, you can use the setup utility to restore the master database. You should not have to go through the VS install just to install SQL, even if it is the express edition. Try the following:
http://msdn.microsoft.com/en-us/library/ms143697(SQL.90).aspx
Joie Andrew
"Since 1982"
December 7, 2009 at 3:58 am
How about trying to create a new named instance using add/remove programs!
Pavan.
December 7, 2009 at 5:56 am
Try starting SQL Server in single user mode:
If SQL Server 2005 is started in single-user mode, any user who has membership in the BUILTIN\Administrators group can connect to SQL Server 2005 as a SQL Server administrator. The user can connect regardless of whether the BUILTIN\Administrators group has been granted a server login that is provisioned in the SYSADMIN fixed server role. This behavior is by design. This behavior is intended to be used for data recovery scenarios.
(http://support.microsoft.com/default.aspx?scid=kb;en-us;932881&sd=rss&spid=2855)
HTH
December 7, 2009 at 8:14 am
Gary,
I feel bad for you man.Here is a suggestion.This may not sound conventional but it may just work.Download SQL Express and install a new instance.Stop the SQL express instance that you were unable to access.Connect to the new instance and attach all the databases you may have created in the old instance.After you do this ,just remove the old instance.Hopefully this should work.
December 7, 2009 at 8:44 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply