Removing the GRANTOR reference

  • I have a table driven script that will automatically add a role with a set of associated GRANTs. When

    I create the role and later verify it's permissions, it shows the correct GRANT but with a GRANTOR of "dbo". I may be fickle about this, but I'd like to remove the reference to dbo, to reduce clutter, because now every permission is doubled: one where the Grantor is blank and the other where it's dbo.

    I've tried scripting it out, but the T-SQL creates it anyway. Code outline is below:

    Suggestions?

    use MyDbName

    go

    create role dboNewRole

    go

    GRANT ALTER ON SCHEMA::dbo TO dboNewRole

    GRANT DELETE ON SCHEMA::dbo TO dboNewRole

    GRANT EXECUTE ON SCHEMA::dbo TO dboNewRole

    GRANT INSERT ON SCHEMA::dbo TO dboNewRole

    GRANT REFERENCES ON SCHEMA::dbo TO dboNewRole

    GRANT SELECT ON SCHEMA::dbo TO dboNewRole

    GRANT UPDATE ON SCHEMA::dbo TO dboNewRole

    GO

  • In SSMS:

    Tools -> Options -> SQL Server object explorer -> Scripting: in Object scripting options check the Script permissions.

    Igor Micev,My blog: www.igormicev.com

  • Currently, these are the settings:

    Script owner False

    Script permissions False

    When I changed them to True, there was no difference when I re-ran the code.

  • I found https://connect.microsoft.com/SQLServer/feedback/details/797967/ssms-create-script-duplicates-the-statements-for-grant-or-deny-column-permissions and the response from Microsoft is that this has been fixed. I have not made research to see if the fix is included in any of the most recent cumulative updates to SQL 2012.

    Note that if you install the update, it's important that you apply on the machine where you run SSMS, because this is a bug in SSMS, not the engine itself.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Thanks. We have SQL 2012 sp1 CU7, dated November 13, 2013

    The link you provided says it was fixed in CU8, dated January 20, 2014. Will check it out.

Viewing 5 posts - 1 through 4 (of 4 total)

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