public class ben:harrell

January 4, 2013

New updates for Cannot open user default database post

Filed under: database, SQL Server 2005, Visual Studio — Tags: , , , , , , — benjamin harrell @ 1:24 pm

After writing this post we were fortunate enough to have many people (all smarter than me) to comment with the various solutions especially for different versions so I have updated the post with the most helpful comments so that they can be found more quickly.  Good Luck!

Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

September 18, 2007

SSIS Custom Component – ProvideComponentProperties vs ReInitializeMetadata

Filed under: .NET, Custom Source Component, database, Integration Servicees, SQL Server 2005, SSIS, Technology — benjamin harrell @ 9:47 am

 

I am currently working on a custom source component in SSIS that converts EBCDIC data to ASCII inline and one of the challenges I face is creating dynamic outputs and output columns based on the layout process of the source component.  Normally, when you want to additional outputs on your component you create them in by override ProvideComponentProperties like this:

public override void ProvideComponentProperties()
{
    // add the outputComponentMetaData.UsesDispositions = true;
   IDTSOutput90 output =     ComponentMetaData.OutputCollection.New();
   output.Name = “My New Output”;
   output.ExternalMetadataColumnCollection.IsUsed = true;

This works really well if all of your output information is available at design time (in the SSIS ui) but what happens if your dynamic outputs are determined at runtime?  ProvideComponentProperties is only called one time, when the component is added to designer surface.  In order to dynamically add outputs at a later point you must use ReInitializeMetaData which is called whenever Validate returns VS_NEEDSNEWMETADATA.

public override void ReinitializeMetaData()
{
   // add the output
   ComponentMetaData.UsesDispositions = true;
   IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
   output.Name = “My New Output”;
   output.ExternalMetadataColumnCollection.IsUsed = true;
 }
 

Note that I have not shown the additional work of adding columns to either of these scenarios, you will need to add that code yourself. 

July 14, 2007

SSIS Row Limits and DefaultBufferMaxRows (Part 2)

Filed under: .NET, Custom Source Component, database, Integration Servicees, SSIS — benjamin harrell @ 10:44 am

I believe the mystery is solved and I’m sad to say that (as usual) it was something silly but crucial.  In a custom source component you must create a new PipelineBuffer in order to write your rows out.  SSIS has provided a way for us to let the engine know when we are done adding rows with a simple method “SetEndOfRowset”.  The name says it all and even the MSDN documentation is clear that you must call this.  In addition, your error logs if you don’t call this method will say something like:

The PrimeOutput method on <your component> returned success, but did not report an end of the rowset. 

 You would think that this error message would be enough to warn any developer that they were missing a key line of code but sadly that wasn’t the case.  In my case I had actually allocated 2 buffers, 1 for data rows and 1 for error rows.  I set a breakpoint on my SetEndOfRowset call and watched it execute so I just knew this wasn’t my problem.  But I forgot the second buffer!  So just a note to all you brave souls commanding bits on the SSIS battlefield.  Call SetEndOfRowset for EACH buffer you allocate. Good Luck! 

July 10, 2007

SSIS Row Limits and DefaultBufferMaxRows

Filed under: C#, database, errors, Integration Servicees, SQL Server 2005, SSIS, Technology — benjamin harrell @ 9:51 pm

I had an odd issue during some testing I was doing on a custom source component I’m writing for EBCDIC data conversion.  I first assumed that surely my code was at fault but the test just seemed to simple to be “my code”.  So here’s the scenario,  I have a file with 100,000 bytes of the number ‘1’ in EBCDIC.  I created a simple package to import the file as 1 byte rows into a Dataset Destination.  I was surprised when I executed the package and found that only 65,536 rows were processed.  Now if you have been around software for more than a week you probably recognize this magic number and unfortunately it triggers all sorts of theories about what could possibly be going wrong.  Well, I’m still trying to investigate the “why” and I’ll update this post and welcome comments if anyone has input but for now the solution to this oddity is to increase the DefaultBufferMaxRows to greater than your number of records.  This seems like a horrible workaround for a situation that should happen quite often so the hunt continues…

June 18, 2007

UPDATE: Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

Filed under: database, dba, SQL Server 2005 — benjamin harrell @ 10:24 am

 Thanks to Fritz Laurel for adding this in the comments section, after you gain access you may need to run this script to store the new default database for the Login you used.

ALTER LOGIN loginid
WITH DEFAULT_DATABASE = master
GO

January 15, 2007

Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

Filed under: database, dba, errors, SQL Server 2005 — Tags: , , , , , , — benjamin harrell @ 8:28 pm

Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

This error bugged the crap out of me tonight and I saw all kinds of crazy answers usually involving some sort of SQLCMD -blah -blah…i think this maybe the problem with command line happy developers/dbas but to make a long story short the fix is EASY and you will kick yourself for not trying this.

1.  click the options button if the options are not showing.

2.  click the connection properties tab if it is not active

3.  click on the Connect to database: dropdown

4.  type in the name of a database that still exists (ex. ‘master’)

5. connect!

I heard all kinds of rants and raves about how atrocious it was that microsoft did this to us and I agree it does suck that SQL Server doesn’t do something about this automagically but what would you suggest it do?

 

 

[UPDATE]  I wanted to move some of the great ideas in the comments to the top so that people could find other solutions more quickly:

From Commenter Fritz Laurel:

ALTER LOGIN loginid
WITH DEFAULT_DATABASE = master
GO

From commenter Das:

For those of you who use their Domain accounts, here is the Alter statement to run once you assign the MASTER DB from the above step.

ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

Replace [BUILTIN\Administrators] with your [domain\usr]

NOTE:[Brackets] required on Domain\User accounts

From commenter Breb02 (SQL 2000?)

RUN–>cmd
sqlcmd -E -d master (osql -E -d master for SQL 2000)
1>sp_defaultdb ‘Computer\username’, ‘master’
2>go

Later SQL Server versions:
sqlcmd -E -d master

From commenter Fuad Ahmed

And the solution for me was just switching the registration property of the server from sql authentication to windows authentication.

I think I was lucky that the login sa had different default database and the windows authentication had different.

From commenter Tony G

I’m using VS2008 Server Explorer. I opened Modify Connection, Advanced Properties, and just set User Instance to true. That allowed my local SQL Server to open the local mdf. HTH

From commenter BobG

I had to.. Under my “registered servers” list, pick the offending server/instance and then right click, choosing “properties”, then choosing “connection properties”, and then type in a database name in the offending server/instance in the “Connect to database:” drop down box.

Then I was able to get in but no offered choices from this post worked for me beyond getting back in, and I had very little rights/access.
I wound up deleting my login from the Server/instance under “Security”. Then I was able to close MS SQL Studio and went back in without further issue.
Note: I also have that same account, a domain account, that I had deleted listed under other SQL Domain Groups for the offending server/instance.

From commenter PeterG

I am running Win7x64 and once of a sudden could not open my DB anymore. I ended up having to “take ownership” of the folder with the DB inside and viola – it worked

From commenter Adrian

To repair an an SQL Server 2000 with default db detached I made the following :

1. I used SQL Server 2005 management console from a client PC and get connected to the server to another existing database;

2. Created another user :
EXEC sp_addlogin ‘Adrian’, ‘p1234′

3. Re-attached the default database with :
EXEC sp_attach_db @dbname = ‘ATOTAL’,
@filename1 = ‘C:\ATOTAL DB\atotal_Data.MDF’,
@filename2 = ‘C:\ATOTAL DB\atotal_LOG.LDF’

From commenter spiritos

I couldn’t connect either (default db not found) so tried this fix but I couldn’t even browse the server to select a new db to use. As a result I started looking at the Security for the db… turns out there was a db that was restored (just happened to be my normal login’s default db!) but in the process some Users had been dropped from the Security folder – including the group that my “normal” login belonged to! Added these groups back in then could connect again!

From commenter One IT Services

You don’t have to use any commands at all, if you still have a login that works then use that to first login to the object explorer.
If you don’t then when logging in make sure you do as stated in the blog which was to click the options button on the Connect to Server Screen.
Then on Connect to Database just click Browse Server, say yes to connect and select any database from the list.
Then once you’re in the Object Explorer, expand Security, Logins and select the username you need to fix.
At the bottom of the Login Properties box you just need to select a Default Database, most likely Master.

From commenter vishalsharma

Note : you have to type master in default database drop down. You won’t see it there in the list.

 

I hope some of these most appreciated comments will help others!

Create a free website or blog at WordPress.com.