Post reply

SQL View - Add users and grant permissions syntax assistance

  • February 28, 2019 at 9:30 am

    #379536

    I have a SQL View that I need to add to a clients server. I need help adding users to the permissions and granting access to the sql view. Can I get some assistance with this? 

    I need to add these two users: mlreport and mltab. Both set to Database role. I need these 2 users set with Grant permissions. Can someone provide me with a sample syntax on this with a generic sql view, so I can add it to my create view syntax?

  • February 28, 2019 at 11:20 am

    #2023228

    Jeffs1977 - Thursday, February 28, 2019 9:30 AM

    I have a SQL View that I need to add to a clients server. I need help adding users to the permissions and granting access to the sql view. Can I get some assistance with this? 

    I need to add these two users: mlreport and mltab. Both set to Database role. I need these 2 users set with Grant permissions. Can someone provide me with a sample syntax on this with a generic sql view, so I can add it to my create view syntax?

    CREATE VIEW SchemaName.YourViewName as .....
    GO
    GRANT SELECT ON SchemaName.YourViewName TO mlreport
    GO
    GRANT SELECT ON SchemaName.YourViewName TO mltab

    Sue

  • February 28, 2019 at 1:25 pm

    #2023236

    Thank you kindly.

  • February 28, 2019 at 3:32 pm

    #2023251

    Please don't grant to the user. Use a database role.

             

        CREATE VIEW SchemaName.YourViewName as .....
    GO
    CREATE Role Reports
    GO
    ALTER ROLE Reports add member mlreport
    ALTER ROLE Reports add member mltab
    go
    GRANT SELECT ON SchemaName.YourViewName TO Reports
    GO

    If you do this once, you'll do it again.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply