SQL Server 2005 schema
A database was created in SQL 2000. We are going to move to sql 2005. So I attached a database to SQL 2005 and now I have a problem with name resolution. When I worked with SQL 2000 I did not put my schema's name before table name (select * from table1). The schema's name is my user name (IQA) and by default the schema name is a name of user who loged in. (in 2000)
Here is a problem with SQL 2005. The schema's name is still IQA. But I need to do select * from IQA.table1.
I created IQA login and IQA user is an onwer my IQA schema but I still can not do a select without schema name. I need to resolve this because VB.Net code has all select statement without schema's name. Need help!!!
[723 byte] By [
oksanaP] at [2007-12-24]
i found that if i do with execute as user='iqa_login' everithing works fine.
select current user shows me dbo. How i can change current user from dbo to iqa?
Apparently, on your original environment your account was not mapped as DBO (i.e. your account was not a member of the sysadmin role, and it was not directly mapped to the DBO in the system tables), but when you attached the same DB in the new system, you became DBO.
Most likely the easier way to go back to the behavior you expect is to find the reason why you are mapped as DBO and take the corrective action. The two most likely possibilities are:
1) You are member of sysadmin. Members of sysadmin are always mapped to dbo on any DB in the system. To keep dbo-like permissions, you can add yourself to the db_owner.
2) You are marked as the DBO for the DB in the system tables, to find out if this is true you can execute the following query:
SELECTname,suser_sname(owner_sid)as owner_nameFROMsys.databases
WHEREname='<your_db_name>'
go
USE<your_db_name>
go
SELECTname,suser_sname(sid)as login_nameFROMsys.database_principals
WHEREname='<your_db_name>'
Go
If you are the dbo for the database, you can transfer the ownership to a different principal using theALTERAUTHORIZATIONONDATABASE::demoTO <new_owner_login_name> syntax.
I hope this information can help to solve your problem.
-Raul Garcia
SDE/T
SQL Server Engine
