permissions

  • how to provide the execute permissions to the stored procedures and select, update, delete rights on the tables to a perticular user?

  • charipg (9/21/2009)


    how to provide the execute permissions to the stored procedures and select, update, delete rights on the tables to a perticular user?

    As mentioned before in previous posts.

    GRANT EXECUTE to [loginname]

    will give execute permissions for procedures for that particular database.

    as for DML Permissions

    it is better to do that at object level so,

    GRANT SELECT on [tablename] to [username]

    GRANT DELETE on [tablename] to [username]

    GRANT INSERT on [tablename] to [username]

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • 5oo tables are present in that db.

    so at a time how to give the permissions.?

  • i want to give these permissions to all tables.

  • There are better ways of doing it but

    USE [databasename]

    GO

    GRANT SELECT TO [username]

    try that and let me know if that works for you.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • its not working.

  • how did you test it, remember that allocating permissions this way, it will not show correctly in the GUI. but the permissions would have been allocated

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • yes, i cheked in GUI after excuting that query.

  • If you right click on the database select properties, look at permissions. you will see the user that you granted the select permission to.

    if you click on that user, then click on effective, you will see select there. as well as if yo8u click on explicit, scroll down to select and you will see grant is ticked.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • thanks........i got it........

  • if you are a OKAY in scripting, you may like it.

    SP_MSFOREACHDB

    SP_MSFOREACHTABLE

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • You are probably best creating DB roles and applying the permissions to that role,

    eg

    USE DBName

    go

    CREATE ROLE UPDATE_ROLE

    go

    GRANT EXECUTE ON [dbo].SP TO [UPDATE_ROLE]

    go

    GRANT SELECT, DELETE, INSERT, UPDATE ON [dbo].[tblStartup] TO [UPDATE_ROLE]

    GO

    Makes sense to do this so you can add users to this role and only need to define the permissions once for the role and then add users to the role.

Viewing 12 posts - 1 through 11 (of 11 total)

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