One of the blog reader asked me how transfer of ownership of schema to another users. Follow the simple script and you will be able to transfer ownership of schema to another user.
ALTER AUTHORIZATION ON SCHEMA::SchemaName TO UserName;
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
7 Comments. Leave new
Hi,
Sorry to say that i followed each of difrent step u sugest for delete duplicate data but its not working in my data base.
1 2 12-Apr-2008 9:00 AM 11:00 PM Saturday 14 4/12/2008 3:17:11 AM 04 Saturday 2008
2 2 12-Apr-2008 7:00 AM 11:00 PM Saturday 16 4/12/2008 3:19:27 AM 04 Saturday 2008
3 2 11-Apr-2008 9:00 AM 10:00 PM Friday 13 4/11/2008 3:24:32 AM 04 Friday 2008
7 2 11-Apr-2008 8:00 AM 10:00 PM Friday 14 4/11/2008 3:28:18 AM 04 Friday 2008
8 2 11-Apr-2008 5:00 AM 10:00 PM Friday 17 4/11/2008 3:29:20 AM 04 Friday 2008
9 2 11-Apr-2008 10:30AM 11:30PM FriDay 12 fbggfb 4/12/2008 3:17:11 AM dfbdfbdfb 04 FriDay 2008
I am faced with this as well. Thank you! I am a newbie so bare with me. I want to see all ownership of schema’s and groups or databases for a particular user account. What would be the easiest way to accomplish this?
@ Dhanu
Can you please discuss in detail .. what exactly you are trying to do …
@ Sam
In object explorer if you click on schema ( Just slingle click as if you are selecting it and on your right hand side in summary section you will see all the schemas and their owner.
To my knowledge there can be only one owner ( database role or database user) for one schema.
If you click user properties ( object explorer, server- database – security- users – double click on the specific user ) you will be able to see what all schemas he owns and what all permission he has, if you click securables in user properties … you will be able to see what all permission the user or database role has on database objects.
how to work with windowsAuthorization in one computer
Hi Pinal,
I have give owned schema db_datareader permission to a user and thru SSMS and now i want to change the db_datawriter ,but i was unable to change because db_datareader was disabled which i was given earlier,
how to enable this option,please let me know…
Thanks
Koteswar Rao
Thanks, Pinal. Exactly what I need to change the schema owner to allow the previous owner to be dropped.
Another score for you. Thanks!