#Set owner of dbschema sql code

I too had to restore a dbdump, and found that the schema wasn't dbo - I spent hours trying to get Sql Server management studio or visual studio data transfers to alter the destination schema. The schema and all its objects will then belong to the specified user or. SELECT TABLE_SCHEMA AS 'schema', TABLE_NAME AS 'name' Purpose With the CHANGE OWNER option, you can assign a schema to another user or role. Select specific_schema as 'schema', specific_name AS 'name' The dbschema utility uses the owner.object convention when it generates any CREATE TABLE.

#Set owner of dbschema sql code

I added an exec to have this code self-execute, and I added a union at the top so that I could change the schema of both tables AND stored procedures: DECLARE cursore CURSOR FOR You can edit dbschema output to change the owner of a new object. To alter the owner, you must also be a direct or indirect member of the new owning. The Name Filter: field allows filtering of objects using SQL wildcards appropriate to the DBMS of. I just posted this to a similar question: In sql server 2005, how do I change the 'schema' of a table without losing any data?Ī slight improvement to sAeid's excellent answer. To rename a schema you must also have the CREATE privilege for the database. The Import DB Schema from ODBC Source dialog Name Filter.