Grant privileges via cursor

  • I am trying to set up a nice fairly robust install script. Part of it is to set up permissions on tables for specific users.

    I thought a nice easy way to do it would be to grab the table names in a cursor and then use grant on each record in the cursor like so:

    declare @tablename varchar(50)

    declare tablenames cursor for

    (select table_name from information_schema.tables where table_name like 'EC%')

    open tablenames

    fetch tablenames into @tablename

    while @@fetch_status = 0 begin

    GRANT ALL On @tablename TO EcourtAdmin

    --print @tablename

    fetch tablenames into @tablename

    end

    close tablenames

    deallocate tablenames

    Which, if I just print the names, works fine. but when I switch to actually assigning the permissions I get failure. If I type in the table names specifically it works.

    I assume I have used the wrong type for @tablename. Ideas?

  • You'll have to build up a string and use Exec().

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

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

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