creating schema within a database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

creating schema within a database

Author

Topic

lesa
Starting Member

1 Post

Posted - 2004-09-23 : 09:53:45

Has anyone created a separate schema within a database on SQL 2000 with an owner other than dbo for use by an application? If you have, could you please tell me how?

I have tried to use the code example in "Books on Line" but when I run the script it ends completed successfully and I can't see the new schema. What should I be looking for?

We need to use schemas since we will be housing different companies' data and we can't create separate databases for licensing reasons.

Any help would be greatly appreciated!

Kristen
Test

22859 Posts

Posted - 2004-09-23 : 10:31:48

Isn't this just a question of doing

CREATE TABLE XXX.MyTable

instead of

CREATE TABLE MyTable
or
CREATE TABLE dbo.MyTable

or am I missing something?

I suppose you will also need some permissions which mean that when XXX is logged in they only see XXX.MyTable and not YYY.MyTable (and certainly not dbo.MyTable!)

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 13:34:37

Creating separate databases on the same instance does not require extra licenses.

Does the app even support the objects being owned by a non-dbo account?

Tara

Kristen
Test

22859 Posts

Posted - 2004-09-24 : 04:35:25

I wondered about the APP too ... but I suppose if the APP just does "Select * from MyTable" (with the "dbo." prefix), and easch user has appropriate assigned table permissions, then the appropriate MyTable will just get used, won't it?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources