none
create user without login - more than one db by app

    Question

  • Hello

    I am looking into creating a user without login to be used by the application however the app uses more than one db. Is it still viable and if so, how?

    1- create db user without login on DB1

    2 - GRANT IMPERSONATE ON USER to a Domain account user

    3 - create db user without login on DB2 ?

    4 - GRANT IMPERSONATE ON USER to the same Domain account user used in step 2?

    Suggestions?

    Thanks!

    Wednesday, March 12, 2014 12:08 AM

Answers

  • When you create a user without a login you don't specify if it is a Windows or SQL user it is just a db user.

    Yes, but you can say

       CREATE USER thisuser WITHOUT LOGIN

    or

       CREATE USER [Domain\User] WITHOUT LOGIN

    And in the later case, this must be a valid Windows login.

    The app is both web based and desktop.

    So there is always an application server? Or are there direction connections from the desktops to the server?

    Then you can "tie" it to a Windows Domain Account with GRANT IMPERSONATE ON USER to a Domain account user
    ...
    I am implementing security best practices and am trying to avoid logins to sql directly and only through the app.

    What does this loginless user represent? The actual user?

    Sorry for asking all the questions, but I want to verify that you have a secure setup before I suggest anything.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 12, 2014 4:17 PM
  • [There have been some problems with the NNTP bridge which I use for posting today. So in case, the problem still persists: this post is from Erland Sommarskog, whatever it says at the bottom.]

    The loginless user would be used by the app. I was just trying to prevent developers from logging into the server via management studio using the sql server user used by the app currently. Connections from the web and desktops.

    I'm not sure that I see how this could work. Anything the app can do, the devs can do from Mgmt Studio if they know the credentials for that app uses.

    [I can confirm that this reply was written by Erland Sommarskog. //Stefan Blom, Word MVP]

    Thursday, March 13, 2014 10:39 PM

All replies

  • Hello,

    Do you mean several contained databases, where you can create a database user without a SQL Server login?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, March 12, 2014 6:55 AM
  • Well yes and no.

    I am looking at the app role user functionality where the app has no sql server login however the app uses more than one database on the sql server instance so maybe it must have a login?

    Thanks

    Wednesday, March 12, 2014 1:09 PM
  • I think it will be difficult to tie the users in the different databases together. They may have the same name, but the SID is different. But, hm, what if you use the same SID everywhere?

    Would this be for an SQL login or a Windows login? Ah, you say application role on the second post. An application role is more or less sandboxed into one database. You could have different application roles and change depending on which database you access. But then cross-database queries will be difficult.

    But maybe I should go back and ask why you are using application roles in the first place. And moreover, I should ask what the architecture of application is. Desktop? Web app? Is there an application server?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 12, 2014 1:29 PM
  • When you create a user without a login you don't specify if it is a Windows or SQL user it is just a db user.

    Then you can "tie" it to a Windows Domain Account with GRANT IMPERSONATE ON USER to a Domain account user

    So as far as SIDs go the same Domain Account would have the same SID but the user without the login on two different dbs ... that is my question. I don't think this is possible.

    I am implementing security best practices and am trying to avoid logins to sql directly and only through the app.

    The app is both web based and desktop.

    Thanks for the replies.

    Wednesday, March 12, 2014 1:42 PM
  • When you create a user without a login you don't specify if it is a Windows or SQL user it is just a db user.

    Yes, but you can say

       CREATE USER thisuser WITHOUT LOGIN

    or

       CREATE USER [Domain\User] WITHOUT LOGIN

    And in the later case, this must be a valid Windows login.

    The app is both web based and desktop.

    So there is always an application server? Or are there direction connections from the desktops to the server?

    Then you can "tie" it to a Windows Domain Account with GRANT IMPERSONATE ON USER to a Domain account user
    ...
    I am implementing security best practices and am trying to avoid logins to sql directly and only through the app.

    What does this loginless user represent? The actual user?

    Sorry for asking all the questions, but I want to verify that you have a secure setup before I suggest anything.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 12, 2014 4:17 PM
  • Thanks Erland. So I could use a Windows account as a loginless user for 2 dbs to be used by the one app?

    The loginless user would be used by the app. I was just trying to prevent developers from logging into the server via management studio using the sql server user used by the app currently. Connections from the web and desktops.


    Paula

    Thursday, March 13, 2014 1:52 PM
  • [There have been some problems with the NNTP bridge which I use for posting today. So in case, the problem still persists: this post is from Erland Sommarskog, whatever it says at the bottom.]

    The loginless user would be used by the app. I was just trying to prevent developers from logging into the server via management studio using the sql server user used by the app currently. Connections from the web and desktops.

    I'm not sure that I see how this could work. Anything the app can do, the devs can do from Mgmt Studio if they know the credentials for that app uses.

    [I can confirm that this reply was written by Erland Sommarskog. //Stefan Blom, Word MVP]

    Thursday, March 13, 2014 10:39 PM