May 12, 2026 at 1:37 pm
When the schema of an object is changed, SQL Server wipes out the previous set of permission grants. Usually this applies when the permissions are granular. Such as "GRANT SELECT ON abc.MyTable TO Mylogin." And usually, I only care when I have to drop the schema-owning user from the database for whatever reason. What I need to do is ensure that the permissions are added back after I change the schema. Unfortunately, my scripts are not very good at identifying those specific securable permissions.
Scenario is such:
Database: WaltDisneyWorld. Mortimer Mouse owns schema Disney, which has a number of tables. Donald Duck has read permission only on table Disney.MagicKingdom. Mortimer is retiring and I can't remove his user name from WaltDisneyWorld because he owns schema Disney.
So I alter the authorization to on Disney.MagicKingdom to Mickey Mouse. As soon as I do that, Donald loses his read permission on the table.
I need to write a script template for myself that finds all non-DB role permissions on the objects in WaltDisneyWorld and scripts them out so I can reapply after altering the schema owner. Donald might be the only one. Or Minnie might have update permissions on Disney.Bowtique and Daisy has column permissions on Disney.Bowtique. Goofy might even have execute permissions on Disney.sp_GoofTroop.
Does anyone have advice on what tables I can use or a good script to validate what securable permissions exist on database objects before a schema authorization change?
Even an article link would help.
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply