Differentiate Schema and Ownership

Both schema and ownership have the same format of schema_name/owner_name.object_name. When I see something like this, how do I know it's a schema or an owership?

more ▼

asked Oct 15, 2009 at 01:14 PM in Default

avatar image

21 1 1 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If it is in SQL Server 2005/2008 then it is Schema.object_name. Now, at time of creation the schema will normally default if no action is taken to the owner's name, but once the object is created the owner name and schema name are separate entities and tracked entirely separately.

If you want to find the objects owner, you have several options, but the easiest is to right click on the object in SSMS and go to properties.

more ▼

answered Oct 15, 2009 at 01:25 PM

avatar image

15.6k 22 51 38

(comments are locked)
10|1200 characters needed characters left

In SQL Server 2000 it's quite simple - everything is the same, because users and schemas are the same thing. The majority of objects are schema-scoped, so it's easy to understand.

In 2005 and 2008, it gets more complex, because a lot more of the objects are spread across differenct scopes. So you have schema-scoped objects, which belong to a schema, database-scoped objects which belong to a database, and server-scoped objects which belong to the server as a whole.

Both schema-scoped and database-scoped objects have owners - thus you can use the ALTER AUTHORIZATION statement to change the owner of an object.

For schema-scoped objects, the default owner is the owner of the schema to which the objects belong.

more ▼

answered Oct 15, 2009 at 03:45 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 15, 2009 at 01:14 PM

Seen: 2298 times

Last Updated: Oct 15, 2009 at 01:29 PM

Copyright 2016 Redgate Software. Privacy Policy