Post reply

Grant permission on a table using Permission from another table

  • August 31, 2005 at 2:54 pm

    #102759

    HI,

    I was wondering if there is a way in tsql to grant the same permission to a table by using the permission from another table. Because i have to drop a table and create it back in a jobs that run every night, i want the table to have the right permission, sure i could giv the permission by using grant and the permission needed, but if i change the permission to the tables in that db for some raison, i dont want to have to change the permission i giv in my jobs, but i want to give the same authorisation to that table by using the authorisation from another table in that database.

    Sry for my english, not my first language, hope it still readable

    thx, Francois.

  • August 31, 2005 at 4:56 pm

    #586687

    I have not really looked into doing that, but it does sound like a useful thing to work out..

    I can think of two approaches.

    1. Rather than using SQL's grant, deny commands directly when you want to grant permissions, have a table where you store this information.  Then have a job that runs every X minutes (eg, 10, 30, 180) that will take the permissions you have set up in your table and use it to create the grant/deny statements as appropriate.  That way, when you drop and recreate your table, you just need to have the job reapply the permissions and you are set.

    2. SQL must store the permissions somewhere - I suggest that you run a profiler trace whilst having enterprise manager bring up the permissions on the table in question - you will be able to see what SQL commands it is executing to work out who or what has permissions on your table.

    If you figure out more on one of these methods, please post as I would be interested in seeing what you come up with.

    Cheers,

       Ian

  • August 31, 2005 at 11:40 pm

    #586722

    A simple way is via Enterprise Manager, list database then list tables in the database.  From here you can right click on the table and select 'all tasks' then 'generate sql script'.  Choose settings you want and ensure that under options that the script object level permissions is selected.  This will create a script to recreate the table including the object level permissions.

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

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