Post reply

Grant permissions with Dynamic Sql

  • June 17, 2005 at 12:19 pm

    #69283

    Hi All,

    There is a table (RoleName, ObjectName, ActionName, ProtectType) with more than 1 record, i.e. everything we need to create GRANT statement. Can I issue all grant statements dynamically without using cursor? For example, create string

    “SELECT ProtectName + ' ' + ActionName + ' ON ' + ObjName + ' TO ' + RolenameNew + ‘ ‘ + 'GO' FROM tbl RolePerm” and execute (NB- table has some records!). Tried sp_executesql, but confused.

    Thanks

     

  • June 17, 2005 at 12:56 pm

    #566543

    Write your select that returns the commands add GO after each command in your select and run the result in QA

    or if you think that your result in commands is less <4k bytes you can do

    declare @myQuery nvarchar(4000)

    Select @myQuery=@myQuery+ "build my commn string" +char(13)+char(10)+'GO'+char(13)+char(10)

    from your table

    sp_execsql @myQuery

     

    Kindest Regards,

    Vasc

  • June 17, 2005 at 1:17 pm

    #566545

    Thanks a lot! Probably, this is what I am looking for.

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

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