locked
how to deny permission to user to access view in same schema RRS feed

  • Question

  • Hi Everyone,

    I have DB name customer
    in that db schema name KF

    i have three user in DB all have access to schema KF

    Alex

    John

    kerry

    I have db role name 

    KFC

    Under Role KFC -> John and Kerry 

    Now i have created two view name

    1) KF.VW_Customer_Bill

    2) KF.VW_Company_Bill

    Scenaro 1:

    I want only KFC user can use the  query to see the data from first view

    select * from KF.VW_Customer_Bill

    and when they will use query for second view they cant see any data 

    select * from KF.VW_Company_Bill

    Scenaro 2:

    I  want alex to see the data from both the queries

    select * from KF.VW_Customer_Bill

    select * from KF.VW_Company_Bill

    is there any possible way so give role base access to view inside schema?

    Thanks

    • Moved by Tom Phillips Tuesday, September 5, 2017 4:56 PM Security question
    Tuesday, September 5, 2017 3:23 PM

All replies

  • Hi Ajay Singh Rawat,

    Basically you can use the following code to achieve your requirement:

    USE customer

    GO

    --create schema and views

    CREATE SCHEMA KF

    GO

    CREATE VIEW KF.VW_Customer_Bill

    AS

    SELECT * FROM [YourTable1]

    GO

    CREATE VIEW KF.VW_Company_Bill

    AS

    SELECT * FROM [YourTable2]

    GO

    --create database users

    CREATE USER [Alex] FOR LOGIN [AlexLogin] WITH DEFAULT_SCHEMA=[KF]

    GO

    CREATE USER [Kerry] FOR LOGIN [KerryLogin] WITH DEFAULT_SCHEMA=[KF]

    GO

    CREATE USER [John] FOR LOGIN [JohnLogin] WITH DEFAULT_SCHEMA=[KF]

    GO

    --create role and add members

    CREATE ROLE [KFC]

    GO

    ALTER AUTHORIZATION ON SCHEMA::[KF] TO [KFC]

    GO

    ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [KFC]

    GO

    ALTER ROLE [KFC] ADD MEMBER [Kerry]

    GO

    ALTER ROLE [KFC] ADD MEMBER [John]

    GO

    --grant permission on VW_Customer_Bill

    GRANT SELECT ON [KF].[VW_Customer_Bill] TO [Alex]

    GO

    GRANT SELECT ON [KF].[VW_Customer_Bill] TO [Kerry]

    GO

    GRANT SELECT ON [KF].[VW_Customer_Bill] TO [John]

    GO

    --grant and deny permission on VW_Company_Bill

    GRANT SELECT ON [KF].[VW_Company_Bill] TO [Alex]

    GO

    DENY SELECT ON [KF].[VW_Company_Bill] TO [Kerry]

    GO

    DENY SELECT ON [KF].[VW_Company_Bill] TO [John]

    GO

    And you can check these users’ rights to the two views by executing the query below:

    SELECT princ.name,

           perm.permission_name,

           perm.state_desc,

           object_name(perm.major_id)

    FROM sys.database_principals princ

           LEFT JOIN sys.database_permissions perm

                  ON perm.grantee_principal_id = princ.principal_id

    WHERE (princ.name = 'John'

           OR princ.name = 'Kerry'

           OR princ.name = 'Alex')

           AND permission_name <>'CONNECT'

     

    If you have any other questions, please let me know.

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; 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 <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    • Proposed as answer by Teige Gao Thursday, September 14, 2017 1:42 AM
    Wednesday, September 6, 2017 7:16 AM
  • Please test the below ..,..(sorry cannot test it right now)

    1

    GRANT SELECT ON VIEW KF.VW_Customer_Bill TO KFC 

    REVOKE SELECT ON  VIEW KF.VW_Company_Bill TO KFC 

    2

    GRANT SELECT ON VIEW KF.VW_Customer_Bill TO ALEX

    GRANT SELECT ON VIEW KF.VW_Company_BillTO ALEX


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 6, 2017 7:21 AM