DB from 2000 to 2005 - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

DB from 2000 to 2005

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-07-24 : 11:20:49

I just imported a database from SQL 2000 to SQL 2005
In SQL 2000 i have two users WEBFORMS WEBFORMSUSER

I see in 2005 it created two schemas and two users.

Database is called WEBFORMS
All tables are dbo.

Is it best to make all the tables become WEBFORMS.Employee
and then have one Schema called WEBFORMS

One user called WEBFORMSUSER that access WEBFORMS Schema.

Any directions would be appreciated

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-07-24 : 11:44:29

If there is nothing in either the WebForms or WebFormsUsers schemas, you may be able to drop them, or at the least ignore them. Schemas are a way of simplifying permissions. You can grant select permissions on a schema, instead of either granting each table individually, or adding someone to the db_datareader role (which would give the user access to EVERYTHING in the database). It sounds like you do not need any of that here, so I would leave everything as is, and let the tables remain under dbo.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-07-24 : 12:21:59

Thanks...

I did remove both the schema and users

I made all tables become WEBFORMS.TABLE
Create schema WEBFORMS
User WEBFORMSUSER assigned to WEBFORMS Schema

Some tables were still dbo.TABLE

When i log in as WEBFORMSUSERS I did select * from
dbo.TABLE and was expecting this not to work but it gave me the records...

I was thinking on select * from WEBFORMS.TABLE would work

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-24 : 14:32:48

Looks like dbo still owns tables. Maybe table has two copies, one owned by dbo and the other owned by WEBFORMS.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-07-24 : 14:46:08

Gosh silly me yes i was changing the schema but i did it the hard way copy table structure and import data and forgot to delete the dbo.TABLE1

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources