locked
Cause of this error: "The login already has an account under a different user name" RRS feed

  • Question

  • I am getting the following error after importing a database schema (from both SQL 2000 and SQL 2005):

    "The login already has an account under a different user name"

    The error points to the generated SQL script for the user under the security folder.

    Anybody able to explain to me the meaning and cause of this error?

    Thanks

    Tuesday, September 5, 2006 3:48 PM

Answers

  • KS2006 -

    Is the user in question a member of the sysadmin role? I know that the error will occur when you try to add yourself this way,  because the user is the owner of the design-time validation database, and as dbo, they already exist. But I'd only seen this error when someone tries to add themselves as a user, not when they're adding someone else.

    Tuesday, September 5, 2006 11:13 PM

All replies

  • Hi

    can you provide more information on the user name, is this a standard SQL user that you are trying ot create?

     

    mairead

    PM, TS Data

    Tuesday, September 5, 2006 5:18 PM
  • User name is an active directory user (it's of the form DOMAIN\USERID). This is an error that appears in the "Error List" windows in VS 2005 right after a schema import.

     

     

     

    Tuesday, September 5, 2006 6:11 PM
  • KS2006 -

    Is the user in question a member of the sysadmin role? I know that the error will occur when you try to add yourself this way,  because the user is the owner of the design-time validation database, and as dbo, they already exist. But I'd only seen this error when someone tries to add themselves as a user, not when they're adding someone else.

    Tuesday, September 5, 2006 11:13 PM
  • Steve,

    You are right. The login ID of the user was mapped to the "dbo" user in the database. That is what was causing the error.

    Do you have any suggestion on how to work around this?

    Thanks

    Wednesday, September 6, 2006 3:03 PM
  • The only thing that comes to mind is updating the post-deployment script to add the desired login. Is that a workable solution for you?  I'll let the team know we've got more people bumping into this issue.

    thanks,

     

    Wednesday, September 6, 2006 4:27 PM
  • I encountered the same codition, but with a different resolution. 

     

    SOME-HOW the user group in the database in the restored database was called "SQLAccessGroup {06ab0134-8bbe-40d5-b293-379fe506c4a6}"  and it was associated with the login ID "AGF-TEST\SQLAccessGroup {06ab0134-8bbe-40d5-b293-379fe506c4a6}".  Subtle difference, but did you catch it?  The "AGF-Test\" prefix isn't on the User but it is on the Login.

     
    When the install runs the following code it doesn't see the 'AGF-Test\..." user, so it tries to create the user; however, there is already a User Group associated with the login...(puke)...
    if not exists (select * from sysusers where name = 'AGF-TEST\SQLAccessGroup {06ab0134-8bbe-40d5-b293-379fe506c4a6}') exec sp_grantdbaccess [AGF-TEST\SQLAccessGroup {06ab0134-8bbe-40d5-b293-379fe506c4a6}]
     
    To remedy the situation in SQL Server 2005 I had to delete the Schema group associated with the bad group, then delete the user in BOTH the MSCRM and METABASE databases.  Then I clicked retry and the install completed uneventfully...
     
    Zack Jones ><>
    Q1 Technology
     
     
    Thursday, June 14, 2007 1:26 AM
  • Hi,

    I deleted ALL the users from my VS DB project (I mean there is no files in the folder Users, also there is no text in pre- and post- deployment scripts)

    And despite this fact I'm getting the same error.

    I tried to look deeper into the matter and compared the schemas of the project and the DB to which I had unsucessfully tried to deploy it, and this is what I discovered:

    For some reasons, during the deployment the VS tries to create a new user which has MY windows account, i.e. it tries to execute lines like so:

     

    IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'DOMAIN-282D649\Login')

    CREATE LOGIN [DOMAIN-282D649\Login] FROM WINDOWS

    GO

    CREATE USER [DOMAIN-282D649\Login] FOR LOGIN [DOMAIN-282D649\Login] WITH DEFAULT_SCHEMA=[dbo]

    GRANT CONNECT TO [DOMAIN-282D649\Login]

    GO

     

    * DOMAIN - my domain, Login - my login Smile

     

    So, I'm out of ideas as to why on earth it does so, I have not been able to find any settings which would allow me to switch it off. I'm stuck Sad

     

    Could you suggest the way out, please?

    Thursday, August 23, 2007 6:56 AM
  • I just had at similar problem installing the database for SCCM.

     

    The computer account for the SCCM server had an other login at the SQL server.

     

    I solved the problem using the following commands:

     

    Use Master

    Select * from master.sys.server_principals

    Drop Login ["OldLoginName"]

     

     

     

    Friday, December 7, 2007 12:00 PM
  • Did as you guys suggested and it worked for getting rid of dbo and the user account I was trying to re-create (I added my user account to the Logins, gave it db_owner privileges, and somehow it got named dbo instead of the real user name)!  Thank you!!  I actually ended up using a combination:

    Drop Login ["dbo"]
    Drop Login ["MYDOMAIN\OldLoginName"]
    CREATE LOGIN [MYDOMAIN\OldLoginName] FROM WINDOWS

    -Tom

    • Edited by navyjax2 Monday, October 27, 2008 8:22 PM
    Monday, October 27, 2008 7:24 PM
  • There's actually a much easier way to handle this.

    Add a call to "sp_changedbowner" to "Script.PreDeployment.sql", as shown below:

    EXEC [LocalDatabaseName].[dbo].[sp_changedbowner] @loginame = 'sa', @map = 'true';
    This procedure changes the "dbo" user in the database to the login you specify.  If you have the sa login enabled, I would highly recommend you set the "dbo" user to point the "sa" login.

    The "dbo" user will point to the user who installed SQL Server on the machine.  I deal with this a lot since I usually install SQL Server in my organization.
    • Proposed as answer by kscelfo Friday, January 15, 2010 1:29 AM
    Wednesday, August 5, 2009 3:43 PM
  • Thank you! I could not remember how to fix this issue. You saved me a major headache.
    Friday, February 26, 2010 8:55 PM
  • Ran a trace while hitting "Retry" and found this

    if not exists (select * from sysusers where name = 'YAHADANAI\SQLAccessGroup {22b8400c-fa38-4a26-8b72-82c3f8fa8d4b}')

      exec sp_grantdbaccess [YAHADANAI\SQLAccessGroup {22b8400c-fa38-4a26-8b72-82c3f8fa8d4b}]

    So a simple solution was to remove the user and continue with the installation.

    Im not sure what script-file you are refering to, "Script.PreDeployment.sql" - where is it located?

    Thanks ///M

    Tuesday, September 7, 2010 8:23 AM
  • Im not sure what script-file you are refering to, "Script.PreDeployment.sql" - where is it located?


    The script is created automatically when you create the database project.  Load the database project into Visual Studio, then browse to "<ProjectName>\Scripts\Pre-Deployment" and you'll find the Script.PreDeployment file.  There's also a "Post-Deployment" script (Script.PostDeployment) which is found in a folder named "<ProjectName>\Scripts\Post-Deployment"

    You shouldn't have to remove users every time you deploy.  The previous postings on this thread make it clear that it's rather cumbersome to do that.  The sp_changedbowner stored procedure is a much better way to fix the issue.

    Tuesday, September 7, 2010 1:08 PM
  • You can run the following script to find the misnamed user, then change or drop the user, so you can recreate to assign rights:

    SELECT

    * FROM sys.database_principals where (type='u' or type='s')

    AND SID =

    (

    SELECT SID FROM SYS.SYSLOGINS WHERE [name]='domainName\loginName'

    )

     

    Tuesday, February 7, 2012 4:20 PM
  • Hello Zack, I have the same scenario just like you.  

    I've granted dbcreator server role to one of the domain user called 'domainname\test'.

    Now from some third party application this user account tries to create a database on the server but when he tries that it creates a database on server but it doesn not create any objects inside that database because It gives him the same error "The login already has a account under different user name"  when I tried to run this sql 

    if not exists (select * from sysusers where name = 'domainname\test')  exec sp_grantdbaccess [domainname\test]   --> it gives me the same error.

    What I understand from this is the SID of 'domainname\test' and dbo is same. The user can not create any objects in this database externally it has to be created by application only. I see ppl has same error but dont find resolution, please give me detailed resolution if you can. Thanks in advance.

    Friday, July 6, 2012 5:50 PM