June 25, 2008 at 11:36 am
Hi,
I have a SQL Server 2000 database [A]. When I select records from a table using DATABASE.OWNER.TABLENAME I get fewer records than if I use the TABLENAME only. I'm not sure what's going on here. Please I need to resolve this issue ASAP.
The reason why I need to use DATABASE.OWNER.TABLENAME is because I created a view on another database to point to a table in my [A] database.
Thanks for your help.
June 25, 2008 at 3:12 pm
Run this and see how many rows it will return:
select * from DATABASE.dbo.sysobjects
where name = 'TABLENAME'
_____________
Code for TallyGenerator
June 25, 2008 at 3:17 pm
If you run SELECT * FROM tablename then you will get records returned from a table owned by yourself it such a thing exists or as a fall back a table owned by the database owner.
Try SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Your table name'
This will list the owner/schema as well as the table.
June 25, 2008 at 4:54 pm
Wow you guys not only are quick but you're good.
I run the query you suggested and I get two records:
TABLE_CATALOGTABLE_SCHEMATABLE_NAMETABLE_TYPE
MyDBdboTASKBASE TABLE
MyDBprivuserTASKVIEW
How can I change the database owner so that everything belongs to 'privuser' ?
Thanks.
///////
Actually I just did the following:
- Detach the database.
- Attach the database, but here I picked the correct database owner. In my case that would be privuser user. I run the query that I was having problems with and everything seems to be working fine. Do you guys see any problem with this approach?
Thanks.
June 26, 2008 at 1:16 pm
It is not a good idea to have objects owned by people other than the dbo.
It is also not a good idea to rely on SQL Standard security especially in pre SQL2005 editions. The passwords can be cracked using a pencil and paper.
To change the owner of an object the system stored proc is sp_changeobjectowner
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply