locked
SQL Server User Login Restrict to Particular Database RRS feed

  • Question

  • Hi

       I have created user login called "login1". There are two databases on Servers "MYDB1" and "MYDB2". I want user "login1" to access only database "MYDB1" , but not "MYDB2" (So if "login1" use "use MYDB2" command on sqlcmd it should give error").

       Currently under Login Properties->User Mapping for "login1" has only database checked is "MYDB1". I have also under "Securable->View any Database" "Deny" options is checked.

       But even though after all these settings "login1" can access the database "MYDB2" (Yes it can't access the objects in the database). BTW I am using "Application Role" in my application so "guest" account is enable on both databases so I can create view in one database based on data in other database. So I don't have option to turn off guest account. So am I still missing any settings so "login1" can't access the "MYDB2" (e.g. "use MYDB2" should give error)?

    thanks


    Drew

    Tuesday, July 16, 2013 12:56 PM

Answers

  • >However, did you try to create a user for the login in MYDB2 and in there do DENY CONNECT TO <username>?

    Yes, this works.

    use master
    drop database mydb1
    drop database mydb2
    drop login login1
    
    
    go
    create database mydb1
    create database mydb2
    
    create login login1 with password = 'P@ssword'
    go
    use mydb1
    create user login1 for login login1 
    
    go
    
    use mydb2
    grant connect to guest
    create user login1 for login login1
    deny connect to login1
    go
    use mydb1
    
    execute as login='login1'
     use mydb2 --fails
    revert 
    

    Note that login1 will not be able to access any objects in mydb2, even via cross-database ownership chains.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Fanny Liu Wednesday, July 17, 2013 6:15 AM
    • Marked as answer by Fanny Liu Tuesday, July 23, 2013 2:46 AM
    Tuesday, July 16, 2013 3:13 PM

All replies

  • As you probably realized, "guest" enabled is the reason why the login in question can access the database. I can't comment on whether enabling guest is the only way to handle the view accessing data in a different database issue, I'll leave that to other to comment on.

    However, did you try to create a user for the login in MYDB2 and in there do DENY CONNECT TO <username>? I haven't tested this myself, but perhaps worth a shot?


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, July 16, 2013 2:48 PM
  • >However, did you try to create a user for the login in MYDB2 and in there do DENY CONNECT TO <username>?

    Yes, this works.

    use master
    drop database mydb1
    drop database mydb2
    drop login login1
    
    
    go
    create database mydb1
    create database mydb2
    
    create login login1 with password = 'P@ssword'
    go
    use mydb1
    create user login1 for login login1 
    
    go
    
    use mydb2
    grant connect to guest
    create user login1 for login login1
    deny connect to login1
    go
    use mydb1
    
    execute as login='login1'
     use mydb2 --fails
    revert 
    

    Note that login1 will not be able to access any objects in mydb2, even via cross-database ownership chains.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Fanny Liu Wednesday, July 17, 2013 6:15 AM
    • Marked as answer by Fanny Liu Tuesday, July 23, 2013 2:46 AM
    Tuesday, July 16, 2013 3:13 PM