locked
Grant access to myview to new user RRS feed

  • Question

  • I have created a view which is the union all of two tables in same database, i would like to grant access to my new user who can have only read permission to my view not access to tables , please suggest how can I achieve this.

    respond with example will be appreciated .

    Regards

    Zareen Soomro

    Wednesday, January 20, 2016 5:59 AM

Answers

  • GRANT SELECT on view TO newuser

    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, January 20, 2016 6:47 AM
  • Hi Zareen,

    Adding to other post, if you have the view in a different schema than the tables, the two schemas should have same owner, otherwise you will get error when selecting from the view even if you grant permission on the view to the new user .

    You can review the following example to examine it. If you don’t execute “ALTER AUTHORIZATION ON SCHEMA::test TO dbo” to change owner of test schema, you will fail to select data from test.v view when executing as newuser.

    Use [Yourdatabase]
    
    CREATE USER newuser for login test1;
    
    GO
    
    CREATE TABLE dbo.a(id INT);
    
    CREATE TABLE dbo.b(id INT);
    
    GO
    
    CREATE VIEW test.v 
    
    AS 
    
      SELECT a.id FROM dbo.a union all select * from dbo.b ;
    
    GO
    
    GRANT SELECT ON test.v TO newuser;
    
    GO
    
     ALTER AUTHORIZATION ON SCHEMA::test TO dbo;
    
    GO
    
    EXECUTE AS USER = N'newuser';
    
    GO
    
    SELECT id FROM test.v;
    
    GO
    
    SELECT id FROM dbo.a;
    
    GO
    
    REVERT;
    



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Wednesday, January 20, 2016 7:42 AM

All replies

  • GRANT SELECT on view TO newuser

    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, January 20, 2016 6:47 AM
  • Hi Zareen,

    Adding to other post, if you have the view in a different schema than the tables, the two schemas should have same owner, otherwise you will get error when selecting from the view even if you grant permission on the view to the new user .

    You can review the following example to examine it. If you don’t execute “ALTER AUTHORIZATION ON SCHEMA::test TO dbo” to change owner of test schema, you will fail to select data from test.v view when executing as newuser.

    Use [Yourdatabase]
    
    CREATE USER newuser for login test1;
    
    GO
    
    CREATE TABLE dbo.a(id INT);
    
    CREATE TABLE dbo.b(id INT);
    
    GO
    
    CREATE VIEW test.v 
    
    AS 
    
      SELECT a.id FROM dbo.a union all select * from dbo.b ;
    
    GO
    
    GRANT SELECT ON test.v TO newuser;
    
    GO
    
     ALTER AUTHORIZATION ON SCHEMA::test TO dbo;
    
    GO
    
    EXECUTE AS USER = N'newuser';
    
    GO
    
    SELECT id FROM test.v;
    
    GO
    
    SELECT id FROM dbo.a;
    
    GO
    
    REVERT;
    



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Wednesday, January 20, 2016 7:42 AM