none
User or role does not exist in the database

    Question

  • Hi,

    I am trying to locally install a CMS for my website. During this installation process, it shows me an error "User or role does not exist in the database. Screenshot of the error:

    CMS Screenshot

    Here's the screenshot of the login properties from Management Studio (Properties of the user Sarin)

    CMS Screenshot

    Screenshot shwoing the database user properties: i.imgur.com/9FKfa.jpg

    User mapping: Screenshot: i.imgur.com/ubPAe.jpg

    Monday, January 10, 2011 1:09 PM

Answers

  • Try this script, before uninstalling SSMS.
    EXEC [dbemployee].[dbo].[sp_changedbowner] 'sa', 'true'
    go
    

    SKG: Please Marked as Answered, if it resolves your issue.
    • Marked as answer by optimus_prime Tuesday, January 11, 2011 4:24 PM
    Tuesday, January 11, 2011 3:47 PM
  • I need to drop this user role 'sarin' from dbo before deleting the login? How can I remove 'sarin' from dbo user?
    Execute the below script to map dbo user account to the login sa.
    EXEC [dbemployee].[dbo].[sp_changedbowner] 'sa', 'true'
    go
    

    Once the above script completed successfully, then you can execute the script for the create user sarin for the login sarin .

    Let me know, if it helps.


    SKG: Please Marked as Answered, if it resolves your issue.
    • Marked as answer by optimus_prime Tuesday, January 11, 2011 4:24 PM
    Tuesday, January 11, 2011 4:06 PM

All replies

  • Hello Optimus,

    What kind of permission the user Sarin needs on the database instance?

    If you assign sysadmin fixed server role, the user will be able to perform any activity on the database instance, you don't need to select the other options. The above listed roles are Fixed Server roles, which can not be customized / create a new one.

    As the user is already having a db_owner rights on the database, he should able to perform all the task inside the database 

    I think you might need to choose Create a New database option, so that the installer may add the desired Custom role for the database .

    Hope, this may help :)


    SKG: Please Marked as Answered, if it resolves your issue.
    Monday, January 10, 2011 1:24 PM
  • Hi Sudeepta,

    Thanks for your support! I think there is something very small thing I am missing. I tried with the new database and Umbraco and it was installed successfully. However, I am bound to use an existing database.

    I looked up the properties of the existing datasbe I want to use under the Permission tab. Here's the scrrenshot:

    Screenshot

     

    If I click on Search to add a user to the database, this dialog box appears.

    Screenshot2

    Why isn't the sysadmin 'sarin' visible or how do I have to add that?

    Thirdly, I also want to ask, the passwords for the DBA and Database are different? How can I change/set the password for a database from SSMS Express Studio 2008?

    Monday, January 10, 2011 5:54 PM
  • SQL server needs a Login & an User to provide complete access.

    Login: To log on to the SQL Server (Server-Login Access, No database Access)

    User: Database Access.

    Now back to your question. Let me know, if I am wrong, You need to give access for a existing database ( e.g. TestDB) for the login account Sarin.

    1. Connect to the SSMS --> Go to Security ---> Login --> Right click on the login account Sarin , go to Properties .

    2. Click on the User Mapping tab form the left side menu, select the database (TestDB), in the drop-down list, select the desired permission, e.g. db_owner. Click on Ok to create the user in the database TestDB with db_owner permission.

    I think this is the reason, why you are unable to find the user account in the above screen-shots.

    Let me know, if this helps.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 8:19 AM
  • Hi,

    I have already given db_owner permission to the database I want to use in the User mapping. Screenshot:

    Screenshot

     

    But, I doubt that there is no such user/role in the Database>Properties. When I installed with a new Database, a new user appeared here. So, I think I the user 'sarin' to appear here. Screenshot:

    Screenshot

    How do I add the user 'sarin' here?

    Tuesday, January 11, 2011 8:37 AM
  • Check whether the mapping worked properly. Expand the database (dbemployee) --> Go to Security ---> Expand Users .

    you should found the user (Sarin) here, if the mapping worked properly.

    Most likely, the user account is not created correctly.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 8:59 AM
  • umm... no its not there :-/

     

    Screenshot
    Tuesday, January 11, 2011 10:26 AM
  • Then you need to create it.
    -- For Creating SQL Login
    Use [TestDB]
    GO
    Create User Sarin For Login Sarin with Default_Schema= [dbo];
    go
    -- Adding the db_owner permission for the user sarin<br/>
    EXEC sp_addrolemember 'db_owner', 'sarin'
    go
    

    Change the database name, role name and user name as per your requirement.

    Hope, this will fix your problem.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 10:51 AM
  • I hope I am not bugging you too much :-P Looked like I was real close ;)

     

    Screenshot
    Tuesday, January 11, 2011 11:28 AM
  • Looks like the user account is mapped with a different login account. Execute the following script and provide the output.

    Use [master]
    go
    select loginname, isntuser,isntgroup from sys.syslogins where name = 'sarin'
    go
    


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 11:50 AM
  • Screenshot
    Tuesday, January 11, 2011 12:19 PM
  • Try this script:

    Use [TestDB]
    go
    select * from sys.database_principals dp inner join sys.server_principals sp on (dp.sid = sp.sid)
    go
    


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 12:32 PM
  • Executed the above script without any errors. But, user 'sarin' is still not in the database>security>user>...

    Here's the screencap :-| http://i.imgur.com/FET8A.jpg

    Tuesday, January 11, 2011 12:51 PM
  • Well, then create another user for this login.
    -- For Creating SQL Login
    
    Use [TestDB]
    GO
    Create User Sarin1 For Login Sarin with Default_Schema= [dbo];
    go
    use [testdb]
    go
    -- Adding the db_owner permission for the user sarin<br/>
    EXEC sp_addrolemember 'db_owner' , 'sarin1'
    go

    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 1:34 PM
  • Screenshot
    Tuesday, January 11, 2011 2:18 PM
  • Don't change the login name to Sarin1 . If only the user name is changed, then it will work.

    -- For Creating SQL User
    Use [TestDB]
    GO
    Create User Sarin1 For Login Sarin with Default_Schema= [dbo];
    go
    -- Adding the db_owner permission for the user sarin
    use [testdb]
    go
    EXEC sp_addrolemember 'db_owner', 'sarin1'
    go
    

     


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 2:23 PM
  • Screenshot
    Tuesday, January 11, 2011 2:31 PM
  • Screenshot

    Sorry, my mistake. The problem is, for the login sarin, you have already mapped to the dbo user account. see above screen-shot. That's why the script to create the user account is failing. 

    Workaround: Open the properties of Sarin, and uncheck the checkbox next to database dbemployee. Click on Ok. it will remove the mapping. then execute the first script, I gave to create user sarin .

    It will work. ;)


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 2:39 PM
  • Looks like it's not my day :-(

    Screenshot
    Tuesday, January 11, 2011 2:44 PM
  • Try the following script, which will map the dbo user account to the login sa .

    EXEC [dbemployee].[dbo].[sp_changedbowner] @loginname='sa', @map='true'
    go
    
    Then try to execute the script to create user sarin.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 3:07 PM
  • Screenshot

    I have also started getting error ""Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." within Visual Studio. Oh my! My primary work is within VS and now I can't create databases in VS. :-|

    I am thinking to uninstall SSMS and reinstall to get rid of this problem in VS. But before that I would want to try this "How can I Modify the SQLExpress service to use my local or system account?" :-| :-|

    Tuesday, January 11, 2011 3:31 PM
  • You can change it in SQL Server Configuration Manager--> SQL Server Services.Double click on the service SQL Server(SQL Express), go to properties. there you can change it.

    You don't need to uninstall SSMS, rather than, if possible, delete the login sarin, and create a new login and map it accordingly.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 3:42 PM
  • heh! I am saved! I got overwhelmed. Atleast Visual Studio doesn't show that error now. I'll try deleting the user 'sarin' too in SSMS.
    Tuesday, January 11, 2011 3:45 PM
  • Try this script, before uninstalling SSMS.
    EXEC [dbemployee].[dbo].[sp_changedbowner] 'sa', 'true'
    go
    

    SKG: Please Marked as Answered, if it resolves your issue.
    • Marked as answer by optimus_prime Tuesday, January 11, 2011 4:24 PM
    Tuesday, January 11, 2011 3:47 PM
  • I need to drop this user role 'sarin' from dbo before deleting the login? How can I remove 'sarin' from dbo user?

    Screenshot
    Tuesday, January 11, 2011 3:49 PM
  • The above script was executed successfully. Removed the user 'sarin' from the login too and created a new login, mapped it to dbemployee as db_owner.

    Executed the script

    -- For Creating SQL User
    Use dbemployee
    GO
    Create User Sarin1 For Login sarin with Default_Schema= [dbo];
    go
    -- Adding the db_owner permission for the user sarin
    use dbemployee
    go
    EXEC sp_addrolemember 'db_owner', 'sarin1'
    go
    
    
    Here's the error I am getting

    Screenshot
    Tuesday, January 11, 2011 4:06 PM
  • I need to drop this user role 'sarin' from dbo before deleting the login? How can I remove 'sarin' from dbo user?
    Execute the below script to map dbo user account to the login sa.
    EXEC [dbemployee].[dbo].[sp_changedbowner] 'sa', 'true'
    go
    

    Once the above script completed successfully, then you can execute the script for the create user sarin for the login sarin .

    Let me know, if it helps.


    SKG: Please Marked as Answered, if it resolves your issue.
    • Marked as answer by optimus_prime Tuesday, January 11, 2011 4:24 PM
    Tuesday, January 11, 2011 4:06 PM
  • Guess what? It's done!! :P

    Thanks a lot life saver for being so patient!! :D

    Tuesday, January 11, 2011 4:24 PM