Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've been tasked with moving us from one decade old system (Access '97) to a brand new system using MS SQL Express 2008 as a backend. So great, I know I can connect to the database (successful with a read-only user), the only problem is... I don't have a user in the database with write access, and we don't have source access to the application to know what password the application uses (though, I do have the username).

So, as I can see it, I have two options:

  • Recover the password for the user the application uses in a non-destructive (or undoable) manner.
  • Insert an extra administrative user to the database that I can then use as a data import user.

Are either of these options viable and/or possible, and how would I go about one or both of them, given the fact that I am sitting infront of the computer with full access to do whatever.

share|improve this question
    
Your question is not clear. Are you unsure how to move the data from Access to SQL Express and the security needs? –  Sam Aug 19 '09 at 22:34
    
Re-reading - when you say 'database', you don't specify which one, I think that it making it more unclear. –  Sam Aug 19 '09 at 22:37

2 Answers 2

Typically, this is done by starting SQL Server in single user mode and inserting a new sql admin user that you can then use to gain full access. I would avoid changing the sa password until you know if the application is using it or not.

Here is a link with instructions and screen shots for doing this with normal SQL Server. To put SQL Express in single user mode and open a query window goes something like this:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn> Sqlservr.exe -s SQLEXPRESS -m
C:\Program Files\Microsoft SQL Server\90\Tools\Binn> sqlcmd -S.\SQLExpress

Edit (in case of link rot): Once you have a query window open in single user mode you should be able to add a server admin login that you can use:

CREATE LOGIN MYADMIN WITH PASSWORD='myadminpassword'
SP_ADDSRVROLEMEMBER 'MYADMIN', 'SYSADMIN'

In your case the SQL Logins are enabled or you wouldn't be able to use your read only login, but for others they may need to ensure SQL Logins are enabled by checking or editing your registry (replacing MSSQL.1 with whichever instance you are after, but most likely it is .1). put this in a .reg file and run it (or use RegEdit):

-------------COPY BELOW---------------------------------
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
"LoginMode"=dword:00000002
-------------COPY TILL THIS---------------------------------

And finally if your sa login is disabled, and you want to enable it (but don't do this if your application is using sa or you may break the whole thing):

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO
share|improve this answer

I'm not 100% sure what you're asking. It almost sounds like your application uses a hard coded username and password?!?!?

If your problem is simply resetting the 'sa' account for SQL Server here's how you can reset the 'sa' password (assuming you have physical access to the box).

  1. Login to the physical machine as the Administrator.

  2. Run SQL Server Enterprise Manager.

  3. Right-click the server name and choose "Edit SQL Server Registration properties".

  4. Choose 'Use Windows authentication' and then click OK.

  5. Expand the server, expand Security then click Logins.

  6. Double-click the sa login and specify new password on the General tab.

share|improve this answer
    
The application sets up it's own SQL Server instance. I didn't think of trying to use Windows auth, but it doesn't work (yes, as Administrator). "Cannot connect to FOO\BPSINSTANCE. Login failed for FOO\Administrator." –  Matthew Scharley Aug 14 '09 at 9:05
    
As for it using a hardcoded username/password... I really don't know, but from looking in the Logins section in the readonly account, there's only the 'sa' login and the readonly account (though, now that I think about it, I suppose that could be a permissions issue) –  Matthew Scharley Aug 14 '09 at 9:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.