NEED IMMEDIATE HELP with Scripting

  • Hi All,

    I am trying to generate script for objects on multiple SQL instance for multiple database by right click database-tasks-generatescript-choosing SP to single file per object.

    I get the scripts for the object but with grant permission AS [dbo]. I do not want to have that. Please advise how can I avoid this while scripting.

    Example:

    GRANT EXECUTE ON [Object Name] TO AS [dbo]---- I want to avoid AS [DBO]

  • If you're using the Wizard, when you get to Set Scripting Options, click on the Advanced button. There's a "Script Object Permissions" check box. Make sure that's disabled. That should fix it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for the reply. But my request is to have object level permissions. All I need is to avoid the AS (DBO) part from the permissions. Is there any way to do it in options or any settings.

  • Why are you trying to drop the AS DBO? From BOL:

    AS <database_principal>

    Specifies a principal from which the principal executing this query derives its right to grant the permission.

  • It is for the internal requirement. Is there any way to exclude that part ....AS [DBO]?

  • QuickToLearn (5/7/2015)


    It is for the internal requirement. Is there any way to exclude that part ....AS [DBO]?

    Not that I am aware. You either have to modify the generated script or take the time write your own scripting routines (not an easy undertaking).

  • That's part of the object level permission as defined on the database you're scripting. You could probably write a PowerShell routine to remove it later.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Quick thought, open all scripts in SSMS and do Find and Replace in All open documents

    😎

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

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