Cross database reference select access with disabled login for DB user












3















We have two databases (DB1, DB2) with the following view structure.



DB1:




  • T1, T2, T3, ..... Tn (Tables)

  • V1 (View consuming {T1, T2, T3 ..... Tn })


Inside DB2 we have to create a view V2 which will pull the data from DB1.dbo.V1



USE DB2;
CREATE VIEW dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic


Now the problem statement is, user created for the DB2 should not have any login access to DB1 but should be able to login on DB2 to consume the V2.



I don't want to disable the owner chain setting. Till now we have tried the below solution. But it enables the user to see the selected view (V1) inside DB1.



USE DB1;

CREATE View dbo.V1 AS SELECT * FROM T1; -- And some other tables according to business logic

CREATE USER testUser FOR LOGIN testUser;

GRANT SELECT ON dbo.V1 To testUser;

USE DB2;

CREATE View dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic


Please consider testUser is already present for DB2, I have just mapped it with the DB1 database.



This solution provides me the expected result. But it also allows testUser to login on the DB1 and fetch the data directly from DB1.dbo.V1. Is there any way to deny login access to DB1 but at the same time allowing select access to V2 created inside the DB2?



What we are trying to do over here is to create an user who will have a complete access to DB2. User should be able to fetch the data using the internal views as explained above. But should not able to login to DB1. In Linux/Unix based systems admin can create an internal user which can be used for the access definitions but can not be used for the logging in into system. We are looking solution to implement something similar in SQL.



If we don't grant select access to V1, server throws permission error:




The SELECT permission was denied on the object V1, database 'DB1', schema 'dbo'.











share|improve this question
















bumped to the homepage by Community 9 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.




















    3















    We have two databases (DB1, DB2) with the following view structure.



    DB1:




    • T1, T2, T3, ..... Tn (Tables)

    • V1 (View consuming {T1, T2, T3 ..... Tn })


    Inside DB2 we have to create a view V2 which will pull the data from DB1.dbo.V1



    USE DB2;
    CREATE VIEW dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic


    Now the problem statement is, user created for the DB2 should not have any login access to DB1 but should be able to login on DB2 to consume the V2.



    I don't want to disable the owner chain setting. Till now we have tried the below solution. But it enables the user to see the selected view (V1) inside DB1.



    USE DB1;

    CREATE View dbo.V1 AS SELECT * FROM T1; -- And some other tables according to business logic

    CREATE USER testUser FOR LOGIN testUser;

    GRANT SELECT ON dbo.V1 To testUser;

    USE DB2;

    CREATE View dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic


    Please consider testUser is already present for DB2, I have just mapped it with the DB1 database.



    This solution provides me the expected result. But it also allows testUser to login on the DB1 and fetch the data directly from DB1.dbo.V1. Is there any way to deny login access to DB1 but at the same time allowing select access to V2 created inside the DB2?



    What we are trying to do over here is to create an user who will have a complete access to DB2. User should be able to fetch the data using the internal views as explained above. But should not able to login to DB1. In Linux/Unix based systems admin can create an internal user which can be used for the access definitions but can not be used for the logging in into system. We are looking solution to implement something similar in SQL.



    If we don't grant select access to V1, server throws permission error:




    The SELECT permission was denied on the object V1, database 'DB1', schema 'dbo'.











    share|improve this question
















    bumped to the homepage by Community 9 mins ago


    This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.


















      3












      3








      3








      We have two databases (DB1, DB2) with the following view structure.



      DB1:




      • T1, T2, T3, ..... Tn (Tables)

      • V1 (View consuming {T1, T2, T3 ..... Tn })


      Inside DB2 we have to create a view V2 which will pull the data from DB1.dbo.V1



      USE DB2;
      CREATE VIEW dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic


      Now the problem statement is, user created for the DB2 should not have any login access to DB1 but should be able to login on DB2 to consume the V2.



      I don't want to disable the owner chain setting. Till now we have tried the below solution. But it enables the user to see the selected view (V1) inside DB1.



      USE DB1;

      CREATE View dbo.V1 AS SELECT * FROM T1; -- And some other tables according to business logic

      CREATE USER testUser FOR LOGIN testUser;

      GRANT SELECT ON dbo.V1 To testUser;

      USE DB2;

      CREATE View dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic


      Please consider testUser is already present for DB2, I have just mapped it with the DB1 database.



      This solution provides me the expected result. But it also allows testUser to login on the DB1 and fetch the data directly from DB1.dbo.V1. Is there any way to deny login access to DB1 but at the same time allowing select access to V2 created inside the DB2?



      What we are trying to do over here is to create an user who will have a complete access to DB2. User should be able to fetch the data using the internal views as explained above. But should not able to login to DB1. In Linux/Unix based systems admin can create an internal user which can be used for the access definitions but can not be used for the logging in into system. We are looking solution to implement something similar in SQL.



      If we don't grant select access to V1, server throws permission error:




      The SELECT permission was denied on the object V1, database 'DB1', schema 'dbo'.











      share|improve this question
















      We have two databases (DB1, DB2) with the following view structure.



      DB1:




      • T1, T2, T3, ..... Tn (Tables)

      • V1 (View consuming {T1, T2, T3 ..... Tn })


      Inside DB2 we have to create a view V2 which will pull the data from DB1.dbo.V1



      USE DB2;
      CREATE VIEW dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic


      Now the problem statement is, user created for the DB2 should not have any login access to DB1 but should be able to login on DB2 to consume the V2.



      I don't want to disable the owner chain setting. Till now we have tried the below solution. But it enables the user to see the selected view (V1) inside DB1.



      USE DB1;

      CREATE View dbo.V1 AS SELECT * FROM T1; -- And some other tables according to business logic

      CREATE USER testUser FOR LOGIN testUser;

      GRANT SELECT ON dbo.V1 To testUser;

      USE DB2;

      CREATE View dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic


      Please consider testUser is already present for DB2, I have just mapped it with the DB1 database.



      This solution provides me the expected result. But it also allows testUser to login on the DB1 and fetch the data directly from DB1.dbo.V1. Is there any way to deny login access to DB1 but at the same time allowing select access to V2 created inside the DB2?



      What we are trying to do over here is to create an user who will have a complete access to DB2. User should be able to fetch the data using the internal views as explained above. But should not able to login to DB1. In Linux/Unix based systems admin can create an internal user which can be used for the access definitions but can not be used for the logging in into system. We are looking solution to implement something similar in SQL.



      If we don't grant select access to V1, server throws permission error:




      The SELECT permission was denied on the object V1, database 'DB1', schema 'dbo'.








      sql-server sql-server-2008-r2 permissions






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 25 '16 at 9:31









      Paul White

      52.8k14281457




      52.8k14281457










      asked Nov 24 '16 at 7:29









      SwapnilBhateSwapnilBhate

      479210




      479210





      bumped to the homepage by Community 9 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







      bumped to the homepage by Community 9 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
























          1 Answer
          1






          active

          oldest

          votes


















          0















          • Make sure DB_CHAINING is ON.

          • Map TestUser Login to DB1 and DB2.

          • Grant SELECT on V2.


          • Don't grant SELECT on V1.


          See Ownership Chains in the documentation.



          Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING is ON, when TestUser will SELECT V2, permissions will not be checked on V1, so you don't need to grant it any permission.



          TestUser will be able to retrieve data from V2 but SELECT on V1 will be denied.



          Double-check that DB_CHAINING is ON by executing:



          SELECT name, is_db_chaining_on 
          FROM sys.databases;


          If it's not the case, set it with:



          ALTER DATABASE DBX SET DB_CHAINING ON


          ...and make sure both views have the same owner.



          You can try following code to validate that it's working:



          Connect as sysadmin and create Databases DB1 and DB2



          USE master
          GO
          CREATE DATABASE DB1;
          GO
          CREATE DATABASE DB2;
          GO


          in DB1:



          Create a sample table TB1



          Insert a value in TB1 table (this is only because I dont like empty result set)



          Create a sample view selecting all rows in TB1 table



          USE DB1
          GO
          CREATE TABLE TB1 (id int);
          GO
          INSERT INTO TB1 VALUES(42);
          GO
          CREATE VIEW V1 AS SELECT * FROM TB1;
          GO


          in DB2:



          Create a sample view V2 selecting all rows in DB1.dbo.V1 view



          USE DB2
          GO
          CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
          GO


          Create TestUser Login



          USE [master]
          GO
          CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
          GO


          in DB1:



          Create TestUser User mapped to TestUser Login



          USE [DB1]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO


          in DB2:



          Create TestUser User mapped to TestUser Login



          Grant SELECT on V2 view to TestUser user



          USE [DB2]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO
          GRANT SELECT ON V2 TO TestUser;


          Connect with TestUser Login



          In DB2:



          Try to select from V2



          USE DB2
          GO
          SELECT * FROM V2;


          This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.



          Connect as sysadmin



          Enable DB_CHAINING on DB1



          USE master
          GO
          ALTER DATABASE DB1 SET DB_CHAINING ON;
          GO


          Enable DB_CHAINING on DB2



          ALTER DATABASE DB2 SET DB_CHAINING ON;
          GO


          Connect with TestUser Login



          In DB2:



          Try to Select From V2 again, that time it should succeed



          USE DB2
          GO
          SELECT * FROM V2;


          In DB1:



          Try to Select From V1



          USE DB1
          GO
          SELECT * FROM V1;
          GO


          As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.






          share|improve this answer


























          • The question says, "Is there any way to deny login access to DB1 but…". By suggesting, "Map TestUser Login to DB1 and DB2", your answer appears to fail that specific requirement. If you mean to say there's no way to solve the problem while satisfying it, please consider being explicit about that, otherwise it just looks as though you've overlooked a part of the question.

            – Andriy M
            Nov 24 '16 at 14:18











          • You're right, I realize that I answered to what i thought was asked "between lines" ie Make TestUser able to query V2 but unable to query V1 or any DB1 objects. That's my bad. I don't know any way to solve this without having TestUser in DB1, but I can't say there is no way. If it happens that I'm not answering the question I will delete my answer.

            – Tametale
            Nov 24 '16 at 14:31











          • You can explicitly deny the connect priviledge to a user: use <db>; DENY CONNECT TO <user>;, but if that then still satisfies the DB1-DB2 View chaining eludes me...

            – til_b
            Nov 25 '16 at 9:59






          • 1





            DB chaining will fail if TestUser is denied connect on DB1.

            – Tametale
            Nov 25 '16 at 10:32











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f156246%2fcross-database-reference-select-access-with-disabled-login-for-db-user%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          0















          • Make sure DB_CHAINING is ON.

          • Map TestUser Login to DB1 and DB2.

          • Grant SELECT on V2.


          • Don't grant SELECT on V1.


          See Ownership Chains in the documentation.



          Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING is ON, when TestUser will SELECT V2, permissions will not be checked on V1, so you don't need to grant it any permission.



          TestUser will be able to retrieve data from V2 but SELECT on V1 will be denied.



          Double-check that DB_CHAINING is ON by executing:



          SELECT name, is_db_chaining_on 
          FROM sys.databases;


          If it's not the case, set it with:



          ALTER DATABASE DBX SET DB_CHAINING ON


          ...and make sure both views have the same owner.



          You can try following code to validate that it's working:



          Connect as sysadmin and create Databases DB1 and DB2



          USE master
          GO
          CREATE DATABASE DB1;
          GO
          CREATE DATABASE DB2;
          GO


          in DB1:



          Create a sample table TB1



          Insert a value in TB1 table (this is only because I dont like empty result set)



          Create a sample view selecting all rows in TB1 table



          USE DB1
          GO
          CREATE TABLE TB1 (id int);
          GO
          INSERT INTO TB1 VALUES(42);
          GO
          CREATE VIEW V1 AS SELECT * FROM TB1;
          GO


          in DB2:



          Create a sample view V2 selecting all rows in DB1.dbo.V1 view



          USE DB2
          GO
          CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
          GO


          Create TestUser Login



          USE [master]
          GO
          CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
          GO


          in DB1:



          Create TestUser User mapped to TestUser Login



          USE [DB1]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO


          in DB2:



          Create TestUser User mapped to TestUser Login



          Grant SELECT on V2 view to TestUser user



          USE [DB2]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO
          GRANT SELECT ON V2 TO TestUser;


          Connect with TestUser Login



          In DB2:



          Try to select from V2



          USE DB2
          GO
          SELECT * FROM V2;


          This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.



          Connect as sysadmin



          Enable DB_CHAINING on DB1



          USE master
          GO
          ALTER DATABASE DB1 SET DB_CHAINING ON;
          GO


          Enable DB_CHAINING on DB2



          ALTER DATABASE DB2 SET DB_CHAINING ON;
          GO


          Connect with TestUser Login



          In DB2:



          Try to Select From V2 again, that time it should succeed



          USE DB2
          GO
          SELECT * FROM V2;


          In DB1:



          Try to Select From V1



          USE DB1
          GO
          SELECT * FROM V1;
          GO


          As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.






          share|improve this answer


























          • The question says, "Is there any way to deny login access to DB1 but…". By suggesting, "Map TestUser Login to DB1 and DB2", your answer appears to fail that specific requirement. If you mean to say there's no way to solve the problem while satisfying it, please consider being explicit about that, otherwise it just looks as though you've overlooked a part of the question.

            – Andriy M
            Nov 24 '16 at 14:18











          • You're right, I realize that I answered to what i thought was asked "between lines" ie Make TestUser able to query V2 but unable to query V1 or any DB1 objects. That's my bad. I don't know any way to solve this without having TestUser in DB1, but I can't say there is no way. If it happens that I'm not answering the question I will delete my answer.

            – Tametale
            Nov 24 '16 at 14:31











          • You can explicitly deny the connect priviledge to a user: use <db>; DENY CONNECT TO <user>;, but if that then still satisfies the DB1-DB2 View chaining eludes me...

            – til_b
            Nov 25 '16 at 9:59






          • 1





            DB chaining will fail if TestUser is denied connect on DB1.

            – Tametale
            Nov 25 '16 at 10:32
















          0















          • Make sure DB_CHAINING is ON.

          • Map TestUser Login to DB1 and DB2.

          • Grant SELECT on V2.


          • Don't grant SELECT on V1.


          See Ownership Chains in the documentation.



          Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING is ON, when TestUser will SELECT V2, permissions will not be checked on V1, so you don't need to grant it any permission.



          TestUser will be able to retrieve data from V2 but SELECT on V1 will be denied.



          Double-check that DB_CHAINING is ON by executing:



          SELECT name, is_db_chaining_on 
          FROM sys.databases;


          If it's not the case, set it with:



          ALTER DATABASE DBX SET DB_CHAINING ON


          ...and make sure both views have the same owner.



          You can try following code to validate that it's working:



          Connect as sysadmin and create Databases DB1 and DB2



          USE master
          GO
          CREATE DATABASE DB1;
          GO
          CREATE DATABASE DB2;
          GO


          in DB1:



          Create a sample table TB1



          Insert a value in TB1 table (this is only because I dont like empty result set)



          Create a sample view selecting all rows in TB1 table



          USE DB1
          GO
          CREATE TABLE TB1 (id int);
          GO
          INSERT INTO TB1 VALUES(42);
          GO
          CREATE VIEW V1 AS SELECT * FROM TB1;
          GO


          in DB2:



          Create a sample view V2 selecting all rows in DB1.dbo.V1 view



          USE DB2
          GO
          CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
          GO


          Create TestUser Login



          USE [master]
          GO
          CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
          GO


          in DB1:



          Create TestUser User mapped to TestUser Login



          USE [DB1]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO


          in DB2:



          Create TestUser User mapped to TestUser Login



          Grant SELECT on V2 view to TestUser user



          USE [DB2]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO
          GRANT SELECT ON V2 TO TestUser;


          Connect with TestUser Login



          In DB2:



          Try to select from V2



          USE DB2
          GO
          SELECT * FROM V2;


          This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.



          Connect as sysadmin



          Enable DB_CHAINING on DB1



          USE master
          GO
          ALTER DATABASE DB1 SET DB_CHAINING ON;
          GO


          Enable DB_CHAINING on DB2



          ALTER DATABASE DB2 SET DB_CHAINING ON;
          GO


          Connect with TestUser Login



          In DB2:



          Try to Select From V2 again, that time it should succeed



          USE DB2
          GO
          SELECT * FROM V2;


          In DB1:



          Try to Select From V1



          USE DB1
          GO
          SELECT * FROM V1;
          GO


          As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.






          share|improve this answer


























          • The question says, "Is there any way to deny login access to DB1 but…". By suggesting, "Map TestUser Login to DB1 and DB2", your answer appears to fail that specific requirement. If you mean to say there's no way to solve the problem while satisfying it, please consider being explicit about that, otherwise it just looks as though you've overlooked a part of the question.

            – Andriy M
            Nov 24 '16 at 14:18











          • You're right, I realize that I answered to what i thought was asked "between lines" ie Make TestUser able to query V2 but unable to query V1 or any DB1 objects. That's my bad. I don't know any way to solve this without having TestUser in DB1, but I can't say there is no way. If it happens that I'm not answering the question I will delete my answer.

            – Tametale
            Nov 24 '16 at 14:31











          • You can explicitly deny the connect priviledge to a user: use <db>; DENY CONNECT TO <user>;, but if that then still satisfies the DB1-DB2 View chaining eludes me...

            – til_b
            Nov 25 '16 at 9:59






          • 1





            DB chaining will fail if TestUser is denied connect on DB1.

            – Tametale
            Nov 25 '16 at 10:32














          0












          0








          0








          • Make sure DB_CHAINING is ON.

          • Map TestUser Login to DB1 and DB2.

          • Grant SELECT on V2.


          • Don't grant SELECT on V1.


          See Ownership Chains in the documentation.



          Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING is ON, when TestUser will SELECT V2, permissions will not be checked on V1, so you don't need to grant it any permission.



          TestUser will be able to retrieve data from V2 but SELECT on V1 will be denied.



          Double-check that DB_CHAINING is ON by executing:



          SELECT name, is_db_chaining_on 
          FROM sys.databases;


          If it's not the case, set it with:



          ALTER DATABASE DBX SET DB_CHAINING ON


          ...and make sure both views have the same owner.



          You can try following code to validate that it's working:



          Connect as sysadmin and create Databases DB1 and DB2



          USE master
          GO
          CREATE DATABASE DB1;
          GO
          CREATE DATABASE DB2;
          GO


          in DB1:



          Create a sample table TB1



          Insert a value in TB1 table (this is only because I dont like empty result set)



          Create a sample view selecting all rows in TB1 table



          USE DB1
          GO
          CREATE TABLE TB1 (id int);
          GO
          INSERT INTO TB1 VALUES(42);
          GO
          CREATE VIEW V1 AS SELECT * FROM TB1;
          GO


          in DB2:



          Create a sample view V2 selecting all rows in DB1.dbo.V1 view



          USE DB2
          GO
          CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
          GO


          Create TestUser Login



          USE [master]
          GO
          CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
          GO


          in DB1:



          Create TestUser User mapped to TestUser Login



          USE [DB1]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO


          in DB2:



          Create TestUser User mapped to TestUser Login



          Grant SELECT on V2 view to TestUser user



          USE [DB2]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO
          GRANT SELECT ON V2 TO TestUser;


          Connect with TestUser Login



          In DB2:



          Try to select from V2



          USE DB2
          GO
          SELECT * FROM V2;


          This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.



          Connect as sysadmin



          Enable DB_CHAINING on DB1



          USE master
          GO
          ALTER DATABASE DB1 SET DB_CHAINING ON;
          GO


          Enable DB_CHAINING on DB2



          ALTER DATABASE DB2 SET DB_CHAINING ON;
          GO


          Connect with TestUser Login



          In DB2:



          Try to Select From V2 again, that time it should succeed



          USE DB2
          GO
          SELECT * FROM V2;


          In DB1:



          Try to Select From V1



          USE DB1
          GO
          SELECT * FROM V1;
          GO


          As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.






          share|improve this answer
















          • Make sure DB_CHAINING is ON.

          • Map TestUser Login to DB1 and DB2.

          • Grant SELECT on V2.


          • Don't grant SELECT on V1.


          See Ownership Chains in the documentation.



          Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING is ON, when TestUser will SELECT V2, permissions will not be checked on V1, so you don't need to grant it any permission.



          TestUser will be able to retrieve data from V2 but SELECT on V1 will be denied.



          Double-check that DB_CHAINING is ON by executing:



          SELECT name, is_db_chaining_on 
          FROM sys.databases;


          If it's not the case, set it with:



          ALTER DATABASE DBX SET DB_CHAINING ON


          ...and make sure both views have the same owner.



          You can try following code to validate that it's working:



          Connect as sysadmin and create Databases DB1 and DB2



          USE master
          GO
          CREATE DATABASE DB1;
          GO
          CREATE DATABASE DB2;
          GO


          in DB1:



          Create a sample table TB1



          Insert a value in TB1 table (this is only because I dont like empty result set)



          Create a sample view selecting all rows in TB1 table



          USE DB1
          GO
          CREATE TABLE TB1 (id int);
          GO
          INSERT INTO TB1 VALUES(42);
          GO
          CREATE VIEW V1 AS SELECT * FROM TB1;
          GO


          in DB2:



          Create a sample view V2 selecting all rows in DB1.dbo.V1 view



          USE DB2
          GO
          CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
          GO


          Create TestUser Login



          USE [master]
          GO
          CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
          GO


          in DB1:



          Create TestUser User mapped to TestUser Login



          USE [DB1]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO


          in DB2:



          Create TestUser User mapped to TestUser Login



          Grant SELECT on V2 view to TestUser user



          USE [DB2]
          GO
          CREATE USER [TestUser] FOR LOGIN [TestUser]
          GO
          GRANT SELECT ON V2 TO TestUser;


          Connect with TestUser Login



          In DB2:



          Try to select from V2



          USE DB2
          GO
          SELECT * FROM V2;


          This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.



          Connect as sysadmin



          Enable DB_CHAINING on DB1



          USE master
          GO
          ALTER DATABASE DB1 SET DB_CHAINING ON;
          GO


          Enable DB_CHAINING on DB2



          ALTER DATABASE DB2 SET DB_CHAINING ON;
          GO


          Connect with TestUser Login



          In DB2:



          Try to Select From V2 again, that time it should succeed



          USE DB2
          GO
          SELECT * FROM V2;


          In DB1:



          Try to Select From V1



          USE DB1
          GO
          SELECT * FROM V1;
          GO


          As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 25 '16 at 11:07

























          answered Nov 24 '16 at 11:27









          TametaleTametale

          144




          144













          • The question says, "Is there any way to deny login access to DB1 but…". By suggesting, "Map TestUser Login to DB1 and DB2", your answer appears to fail that specific requirement. If you mean to say there's no way to solve the problem while satisfying it, please consider being explicit about that, otherwise it just looks as though you've overlooked a part of the question.

            – Andriy M
            Nov 24 '16 at 14:18











          • You're right, I realize that I answered to what i thought was asked "between lines" ie Make TestUser able to query V2 but unable to query V1 or any DB1 objects. That's my bad. I don't know any way to solve this without having TestUser in DB1, but I can't say there is no way. If it happens that I'm not answering the question I will delete my answer.

            – Tametale
            Nov 24 '16 at 14:31











          • You can explicitly deny the connect priviledge to a user: use <db>; DENY CONNECT TO <user>;, but if that then still satisfies the DB1-DB2 View chaining eludes me...

            – til_b
            Nov 25 '16 at 9:59






          • 1





            DB chaining will fail if TestUser is denied connect on DB1.

            – Tametale
            Nov 25 '16 at 10:32



















          • The question says, "Is there any way to deny login access to DB1 but…". By suggesting, "Map TestUser Login to DB1 and DB2", your answer appears to fail that specific requirement. If you mean to say there's no way to solve the problem while satisfying it, please consider being explicit about that, otherwise it just looks as though you've overlooked a part of the question.

            – Andriy M
            Nov 24 '16 at 14:18











          • You're right, I realize that I answered to what i thought was asked "between lines" ie Make TestUser able to query V2 but unable to query V1 or any DB1 objects. That's my bad. I don't know any way to solve this without having TestUser in DB1, but I can't say there is no way. If it happens that I'm not answering the question I will delete my answer.

            – Tametale
            Nov 24 '16 at 14:31











          • You can explicitly deny the connect priviledge to a user: use <db>; DENY CONNECT TO <user>;, but if that then still satisfies the DB1-DB2 View chaining eludes me...

            – til_b
            Nov 25 '16 at 9:59






          • 1





            DB chaining will fail if TestUser is denied connect on DB1.

            – Tametale
            Nov 25 '16 at 10:32

















          The question says, "Is there any way to deny login access to DB1 but…". By suggesting, "Map TestUser Login to DB1 and DB2", your answer appears to fail that specific requirement. If you mean to say there's no way to solve the problem while satisfying it, please consider being explicit about that, otherwise it just looks as though you've overlooked a part of the question.

          – Andriy M
          Nov 24 '16 at 14:18





          The question says, "Is there any way to deny login access to DB1 but…". By suggesting, "Map TestUser Login to DB1 and DB2", your answer appears to fail that specific requirement. If you mean to say there's no way to solve the problem while satisfying it, please consider being explicit about that, otherwise it just looks as though you've overlooked a part of the question.

          – Andriy M
          Nov 24 '16 at 14:18













          You're right, I realize that I answered to what i thought was asked "between lines" ie Make TestUser able to query V2 but unable to query V1 or any DB1 objects. That's my bad. I don't know any way to solve this without having TestUser in DB1, but I can't say there is no way. If it happens that I'm not answering the question I will delete my answer.

          – Tametale
          Nov 24 '16 at 14:31





          You're right, I realize that I answered to what i thought was asked "between lines" ie Make TestUser able to query V2 but unable to query V1 or any DB1 objects. That's my bad. I don't know any way to solve this without having TestUser in DB1, but I can't say there is no way. If it happens that I'm not answering the question I will delete my answer.

          – Tametale
          Nov 24 '16 at 14:31













          You can explicitly deny the connect priviledge to a user: use <db>; DENY CONNECT TO <user>;, but if that then still satisfies the DB1-DB2 View chaining eludes me...

          – til_b
          Nov 25 '16 at 9:59





          You can explicitly deny the connect priviledge to a user: use <db>; DENY CONNECT TO <user>;, but if that then still satisfies the DB1-DB2 View chaining eludes me...

          – til_b
          Nov 25 '16 at 9:59




          1




          1





          DB chaining will fail if TestUser is denied connect on DB1.

          – Tametale
          Nov 25 '16 at 10:32





          DB chaining will fail if TestUser is denied connect on DB1.

          – Tametale
          Nov 25 '16 at 10:32


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f156246%2fcross-database-reference-select-access-with-disabled-login-for-db-user%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh