locked
SQL 2005 RRS feed

  • Question

  •  

    Help

     

    i run a sql 2005 enterprise server.  we have 5 custom databases, one called TAT.

     

    we had to upgrade this database and through the console manager dismounted it in order to upgrade.

     

    when i now try to login to the sql system manager if get the following errors:

     

    Cannot connect to [SERVERNAME]

    Additional information:

      - Cannot open user default database. login failed.

      - Login failed for user 'server\administrator'. (Microsoft SQL Server, Error: 4046)

     

     

    what have i done: http://forums.microsoft.com/msdn/showpost.aspx?postid=167068&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

     

    i have tried to set default database back to master i have been able to test etc and all seem good. error still there when i try login.

     

     

    load system management:

     

    click connections: change default database to master.

     

    the management now opens correctly.

     

    i can see all my database, however when ever i try to do properties on anything i get the following error:

     

    cannot show requested dialog:

       - cannot show requested dialog (sqlmgmt)

          - Failed to connect to sql server (servername). (Microsoft.sqlserver.connectioninfo)

             - A connection was successfully established with the server, but then a error occured during the login process.  (provider:  sharedmemory provider, error: 0 - no process is on the other end of the pipe.)(Microsoft SQL Server, Error 233)

     

    if i try to attach the database i detached i get the following:  Unhandled exception has occured in a componant in your application. if you click on CONTINUE, the application .....

     

     

    help please

    Monday, September 1, 2008 2:52 AM

Answers

  •  

    HI Guys

     

    Well its fixed.  what did i do, not much got our sql guru to check it out and this is what he had to do.

     

    Created a new user on the workstation, with full adminitration rights.

     

    logged in to sql management with new user successfully.  (something to do with recreating some of the default sql database.)

     

    once inside sql the default rights where re-setup.

     

    apparently when my customer attempted to upgrade one of his databases, he broke the master database.

     

    thanks for the help.

    Tuesday, September 2, 2008 2:18 AM

All replies

  •  

    It's seem like you don't have the permission what you want to do.

     

    The login can only do operation in master database as guest if he don't have additional permission.

    So make sure the login have enough permission to operate database ,such as attach database...

     

     

    You can execute following script to check your permission in master database:

     

    Code Snippet
    select*from sys.server_permissions  where grantee_principal_id=SUSER_ID(SUSER_NAME())
    select *From sys.database_permissions where grantee_principal_id=DATABASE_PRINCIPAL_ID(current_user)

     

     

     

    Also check the roles you are

    Code Snippet

     

     select *from sys.login_token
     select *From sys.user_token

     

     

    Monday, September 1, 2008 3:55 AM
  •  

    many thanks i willc heck this asap.

     

    One side note, i am using windows authentification not sa.

     

    also i have not had issues until i removed the one database, up until this i have been able to admin all databases.

     

    will check and respond, thanks for the prompt responce.

    Monday, September 1, 2008 4:04 AM
  • Hi stswordman:

     

    i am not to clued up in the scripting side of sql, i can install and sort of maintan it.

     

    those scipted files you ahve sent me do i run them from the sqlcmd command.  treat me like a total newbie.

     

    in short how do i run that code you have placed.

     

    thanks

     

    Monday, September 1, 2008 4:47 AM
  • Hi     michaeljpfister,

     

       I think it's no different between windows user or sql login for this topic.

     

    You have change the default database to master when you login sqlserver, so you can login sqlserver , right?

     

    Although you can login sqlserver successfully, you still recive the error message as you list ?   That 's may because the database which your login bind is null(removed).

     

    You should alter the default database of the login to a exist database such as master database:

    Code Snippet
    alter login loginName with default_database=master

     

     

    Monday, September 1, 2008 7:08 AM
  •  

    HI Guys

     

    Well its fixed.  what did i do, not much got our sql guru to check it out and this is what he had to do.

     

    Created a new user on the workstation, with full adminitration rights.

     

    logged in to sql management with new user successfully.  (something to do with recreating some of the default sql database.)

     

    once inside sql the default rights where re-setup.

     

    apparently when my customer attempted to upgrade one of his databases, he broke the master database.

     

    thanks for the help.

    Tuesday, September 2, 2008 2:18 AM