none
Please Help - Having issues with Logins / User Confusion RRS feed

  • Question

  • Hi,

    I have a local version of SQL Server Express with a couple of databases - both of which i am capable of connecting to and updating via SSMS and VB.NET apps. However ... for some reason i cannot connect via PowerBI. I am a b it new to SQL although i roughly understand the language i get a bit hazy around the world of users, logins, roles etc etc.

    So in my "master" DB i have my windows credential added and i have the user mapping set in SSMS to access the DB in question but the mapped user is "dbo". I cannot change the mapped user as i get an error saying user "dbo" cannot be changed ... When i connect in powerBI i use the server "(LocalDb)\MSSQLLocalDB" which connects fine but no databases are shown. I'm pulling my hair out as to why...

    Tuesday, April 30, 2019 2:55 PM

All replies

  • Does the account you added have read-only access to the database? In other words, is that login mapped to the database and under it, have db_datareader?
    Tuesday, April 30, 2019 6:09 PM
  • localdb is, well, localdb, which is a database engine that runs in user space.

    If you have your databases on the main Express instance, you should connet with (local)\SQLEXPRESS.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, April 30, 2019 9:42 PM
  • Hi Djdave022002,

    Could you try to connect the server with (local)\SQLEXPRESS like Erland said. For SQL Server Express, the instance is named <computer_name>\sqlexpress unless the name was changed during setup. And you can run select @@SERVERNAME in SSMS you check your instance name.

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 1, 2019 2:23 AM
  • Hi, (local)\SQLEXPRESS yields the same result, it appears to connect but lists no Databases. in SSMS my instance name appears to be (LocalDb)\MSSQLLocalDB as it appears in object explorer (NB PowerBI and SQL Express are on the same machine). Running the Select @@SERVERNAME command yields my laptops machine name with some text appendage however attempting to connect to this instance name fails completely with "instance not found" "IT_LPT28\LOCALDB#EBEFB80B"
    Wednesday, May 1, 2019 8:29 AM
  • Hi, the User mapping for the login shows the user for the database as "dbo". also it has role membership of public and db_owner. If i try and add db_datareader it errors with "cannot use the special principle 'dbo'". If i try and amend the mapped user to my windows credential or a user i have manually created on the database i get the same error or "cannot alter the user 'dbo'"
    Wednesday, May 1, 2019 8:35 AM
  • Are you sure of the correct code? 

    Wednesday, May 1, 2019 8:56 AM
  • Sorry which code are you referring to ?
    Wednesday, May 1, 2019 8:59 AM
  • OK, then "(LocalDb)\MSSQLLocalDB" seems to be the correct instance name.

    Then it seems to be a matter with which user you connect with from PowerBI. Do you know how that is set up? (I ask it that way, because I don't know anything about PowerBI myself.)

    Here is some general information that may not help you to resolve the problem as such, but which may be good to know. In SQL Server distinguishes between "logins" and "users". More precisely they are concepts on different levels.

    You use a login to connect to a server. This can be your Windows user (yeah, that is confusing, but Windows calls it a user), or an SQL login and a password. But you cannot connect with a different Windows user and password.

    Connection to the server only gives you access to the system database. To be able to access some other database, your login needs to map to a user in that database, and this is done per database. There is one exception: your login is in the server role sysadmin or have been granted the permission CONNECT ANY DATABASE. Typically in the case of an Express instance and localDB, the Windows user you used when running the installation is sysadmin. (You set this up when installing SQL Server.) Thus, when you connect through SSMS you are typically sysadmin.

    Most commonly, the name of a login and the database user it maps to is the same, but they don't have to. That is, you can create an SQL login called Jack and then from that you can create a database user called Jill. Obviously, that would be a confusing thing to do, and people don't do that very often. But there is one special case: if you own the database or you are sysadmin, it does not matter what your login is. Your username in the database will be dbo (short for database owner). Dbo can do anything inside the database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, May 1, 2019 9:57 AM
  • Hi, i am connecting using my windows account as a login which has dbo and was the same account used to install SQL Express. It is mapped to "dbo" user.
    Wednesday, May 1, 2019 12:45 PM
  • Yeah, but it seems that PowerBI uses something different. Maybe you should ask in a PowerBI forum. They might know PowerBI better there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, May 1, 2019 5:51 PM