Change Schema Ownership using SSMS

Question

Change Schema Ownership using SSMS

aaa121

SSCertifiable

Points: 5902
More actions
April 17, 2007 at 5:20 am

#172072

I have a login which I need to delete for security purposes. The problem is the login owns several objects. I must firstly transfer the schema ownership to the dbo schema.
I need to do this using SSMS and without writing any T-SQL. Can someone explain how to do this please?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Catherine Eibner Ten Centuries Points: 1364 More actions · Answer 1

On the general Tab of the Schema Properties (within the actual database) there is a Schema Owner field and a Search button. Search to who you want your Schema to now be owned by then save your changes. Once your schema is migrated to a new owner you can delete the users.

Catherine

Kindest Regards,

Catherine Eibner
cybner.com.au

bledu SSChampion Points: 12335 More actions · Answer 2

befaore you migrate you might need to check if the schema owner was not used in queries.

e.g. select * from dbo.SomeTable

Everything you can imagine is real.

aaa121 SSCertifiable Points: 5902 More actions · Answer 3

I have searched for a new schema owner and selected dbo, then clicked ok. I presume this saves the changes?

I then go to the users section and right click > delete on the old schema owner. I get error 'drop failed for ' as the user owns a schema in the database.

It is so frustrating!! What am I doing wrong?

Ariadne SSCrazy Points: 2044 More actions · Answer 4

Adam,

I'm experiencing the same problem. I'm about fit to be tied. Mine's a DB role that I was stupid enough to give ownership to another role. That's all I did, so i know it doesn't own anything else... but SMS will not let me delete squat.

Ariadne SSCrazy Points: 2044 More actions · Answer 5

Ahh... got it. Opened the wrong window, and didn't notice. I should have opened the actual Schema dialog box and reassigned the ownership. I was opening the dialog box that was associated with the database role, not the database schema.