NEED IMMEDIATE HELP with Scripting

Question

NEED IMMEDIATE HELP with Scripting

QuickToLearn

Old Hand

Points: 390
More actions
May 7, 2015 at 9:30 am

#303720

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]

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

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

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 1

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

QuickToLearn Old Hand Points: 390 More actions · Answer 2

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

QuickToLearn Old Hand Points: 390 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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).

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 6

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

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 7

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

NEED IMMEDIATE HELP with Scripting

Cookies on SQLServerCentral