Change or add new schema names (1 Viewer)

Hi,
I'm working on migrating Access 2002 db's to SQL Server 2005. I am using the "SQL Server Migration Assistant for Access" (SSMA), which seems like a good tool. In the help file for it there is a tantalizing paragraph:

SQL Server databases use the concept of schemas to separate objects within a database into logical groups. For example, a library database could use three schemas named books, audio, and video to separate book, audio, and video objects from each other.

Click to expand...

But, no information on how to actually DO this. When I do the migration, all the tables, by default, get loaded into MYDatabase as:
dbo.mytable1
dbo.mytable2

etc.

In the tool I have the option to choose, from a pre-specified list, different "target schema" within MYDatabase, so that some of my tables will load as, for example:
guest.mytable3
db_owner.mytable5

But I actually would like to organize my database, for clarity, into something like the quote above indicates, where I would have something like:
books.mytable1
books.mytable2
audio.mytable3
audio.mytable4
audio.mytable5
video.mytable6

etc.

But How do I change "dbo" to "books"?

This Answer relates to SQL Server 2000 ownership, not 2005 Schemas and I am a Dunce who doesn't read the question properly.

There is a good article here: http://www.sqlservercentral.com/articles/Advanced/understandingobjectownership/1966/ (you'll need to register) that explains the basic concepts behind what you're trying to achieve and highlights some of the pitfalls that you need to be aware of when considering things along this line.

Basically, the prefix before the tablename indicates the owner of the object (table, view, stored procedure etc) concerned, dbo in this case indicates that the table is owned by the database owner role within the database.

What you'd need to do is create either Logins or DB Roles called Audio, video, books etc and then create the tables using CREATE TABLE Audio.Tablename for example. By default when you create a table, the ownership will default to the account creating the table (Server Sysadmin logins will create a table using dbo ownership though) but on 2000 at least you should be able to assign ownership to a Role rather than an individual account.

Where you need to careful using this approach is that you can have several objects called the same thing but owned by different entities within your DB. It would be perfectly valid to have tables called:
books.table1
audio.table1
dbo.table1

in the event that you don't explicitly specify the owner SQL server will, in the first instance look for a table owned by the account or the role the account belongs to, if it can't find that it will look for dbo.tablename.

So if an account in the video role tries to query "SELECT * FROM Table1" using the examples above the query will return results from dbo.table1 (assuming it has permission to query the table).

This is a pretty basic description and there are other issues that you'll need to investigate before you steam off renaming all your tables but hopefully it will give you some pointers in the right direction.

Hi there,

Sorry tehnellie I am going to have to disagree with you here, the link you gave refers to object ownership in 2000 sql servers, this is a 2005 question related to database schemas.

To create schema 'books', use the following

CREATE SCHEMA Books AUTHORIZATION <some account>

GRANT SELECT TO <some account>

GO

* where <some account> is the account name you want to use

To add table books to that schema

ALTER SCHEMA Books TRANSFER dbo.Books;
GO

Big oops on my part, I'm going to go sit in a corner and write out 1,000 times "I will check what version of SQL Server people are using before I write an answer".

I'll leave what I wrote up because there may be some other fossils still using 2000, but it's not the right answer to the question.

Hey,

No worries mate, I am probably one of the biggest culprits when it comes to not reading peoples questions properly

So did this help at all Dataminer?