Permissions Removal When Changing Object Schema

Question

Post reply

Permissions Removal When Changing Object Schema

Brandie Tarvin

SSC Guru

Points: 173135
More actions
May 12, 2026 at 1:37 pm

#4829662
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.
EDIT: If I had my druthers, every schema would be created as owned by "dbo," but some of these databases are old and the schemas are already owned by users. Even if I change the owner to "dbo" now, it doesn't change the permission wipe. @=(
- This topic was modified 1 months ago by Brandie Tarvin. Reason: Added note so people don't suggest options that don't change my issue
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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

Site Owners SSC Guru Points: 80224 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

deepeshdhake Say Hey Kid Points: 679 More actions · Answer 2

You can run following script before schema owner change. Save the Output. Rerun the output after the schema owner change.

SELECT dp.class,c.name,
    'GRANT ' + dp.permission_name +
    CASE 
        WHEN dp.class = 3 THEN ' ON SCHEMA::' + SCHEMA_NAME(dp.major_id)
         WHEN c.name IS NOT NULL THEN
            ' ON OBJECT::' + 
            OBJECT_SCHEMA_NAME(dp.major_id) COLLATE DATABASE_DEFAULT + '.' +
            OBJECT_NAME(dp.major_id) COLLATE DATABASE_DEFAULT +
            ' (' + c.name COLLATE DATABASE_DEFAULT + ')'
    END
    + ' TO [' + USER_NAME(dp.grantee_principal_id) + '];'
    AS grant_statement
FROM sys.database_permissions dp
LEFT JOIN sys.columns c
    ON dp.major_id = c.object_id
    AND dp.minor_id = c.column_id
WHERE dp.class IN (1,3)
  AND dp.state = 'G'   -- GRANT only (exclude DENY if you want, or handle separately)
  AND USER_NAME(dp.grantee_principal_id) NOT IN ('dbo','sys','INFORMATION_SCHEMA');

Recommendation: Use database roles instead of direct grants.

ALTER ROLE Disney_Readers ADD MEMBER DonaldDuck;

GRANT SELECT ON SCHEMA::Disney TO Disney_Readers;

This eliminates entire reapply problem.

Deepesh Dhake
Database Administrator