Changing the Owner of Tables in SQL Server
You may configure SQL Server such that users who are not dbo may create tables, but you set yourself up for a world of pain.
Soon, you can no longer do simple table selects (because dbo is inferred) and instead must resort to prefacing every join with [user].[tablename].[fieldname].
The solution is a SQL Server system stored procedure sp_changeobjectowner:
EXEC sp_changeobjectowner 'user.table', 'dbo'
Note that this operation will clear all privileges.
Credit goes to my old co-worker Bruce, who shared this solution many six years ago in an email titled, "Lucky You". I had several hundred tables and views to re-title and privilege at the time.
What is this?
We use the IFWIS Blog to keep our staff, cooperators and the public updated on what we are working on, what's new and share tips about using our software and website.
The content of the IFWIS Blog is often highly technical, but we try our best to keep our posts accessible and jargon-free. When this is not possible, we'll link to other resources online to provide background. Thanks for reading and please follow our blog for updates.
- IFWIS RSS Feed
- Daily Email Digest (All Public Content)
- Or follow just one of the many topic-specific tag feeds or pages below.