Post reply

Table Permissions

  • October 3, 2008 at 2:44 pm

    #193645

    Is there a script out there that will set table permissions for my users, rather than me having to use the GUI to check the Select, Insert, and Update permissions? Any help will be greatly appreciated.

  • October 6, 2008 at 9:35 am

    #881163

    Best practice is to use stored procedures and only grant users EXECUTE permission rather than table permissions, but you can grant permissions at the database level using GRANT or by right-clicking the database in SSMS, selecting Properties, then selecting Permissions. Permissions granted this way affect all tables in the database.

    Greg

  • October 6, 2008 at 11:40 am

    #881260

    I am new to SQL Server and can certainly navigate through the GUI, as you instructed, but I would really like to learn how to setup a stored procedure. Where should I start? Thanks for all your help.

  • October 6, 2008 at 12:11 pm

    #881271

    I tried testing the db permission "Execute", but it returns an error stating that the user does not have Select permissions on the table.

  • October 6, 2008 at 3:57 pm

    #881371

    So, you must have figured out how to create a stored procedure. The procedure and the tables is accesses must have the same owner. In our shop, all database objects are owned by dbo.

    Greg

  • October 7, 2008 at 9:15 am

    #881744

    In order to test, I just followed your instructions using the SSMC. I still don't know how to create a stored procedure. Any help would be greatly appreciated

  • October 7, 2008 at 10:26 am

    #881803

    Okay, sorry. The EXECUTE permission pertains to being able to execute stored procedures.

    I don't write a lot of stored procedures myself as that's done mostly by the developers here. You can get a template by right-clicking on Stored Procedures in a database in SSMS and selecting new stored procedure. You basically want to put any code that will directly access tables i.e. SELECT, DELETE, INSERT, UPDATE in stored procedures.

    There are lots of books and articles about writing efficient stored procedures and BooksOnLine is a great resource.

    Greg

  • October 7, 2008 at 10:46 am

    #881812

    I appreciate all your help. Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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