May 7, 2015 at 9:30 am
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]
May 7, 2015 at 10:00 am
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
May 7, 2015 at 11:59 am
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.
May 7, 2015 at 12:24 pm
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.
May 7, 2015 at 12:38 pm
It is for the internal requirement. Is there any way to exclude that part ....AS [DBO]?
May 7, 2015 at 12:47 pm
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).
May 7, 2015 at 12:56 pm
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
May 7, 2015 at 1:00 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy