SQL2005 sys tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mecn - 29 Mar 2006 22:45 GMT

Hi,

In sql2005, I don't a lot of sys tables in master db.
i.e. sysusers and syslogins?

ANy ideas?

Thanks

Reply to this Message

Gail Erickson [MS] - 30 Mar 2006 01:27 GMT

In SQL Server 2005, system objects are stored in a new system database
called the Resource database. System metadata is exposed to users in
catalog views.
In general, there is an equivalent catalog view for every system table. So,
instead of querying the sysusers table, you should query the sys.users
catalog view.

Another important change related to system metadata is that in SQL Server
2005, the visibility of metadata is limited to securables that a user either
owns or on which the user has been granted some permission. In earlier
versions, metadata was visible to members of the public role (meaning,
anyone with access to the db).

I recommend the following Books Online topics to get you up-to-speed on
these changes.
Resource Database
Catalog Views (Transact-SQL)
Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
Querying the SQL Server System Catalog FAQ
Metadata Visibility Configuration

Regards

Signature

Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

> Hi,
>
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message

mecn - 30 Mar 2006 15:15 GMT

Thanks,
One more question, In sql2000 I can run query analyzer and in the same
window i could change the the dbs in the server.
In sql2005, I couldn't WHY?

THnaks

> Hi,
>
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message

Gail Erickson [MS] - 30 Mar 2006 18:15 GMT

> One more question, In sql2000 I can run query analyzer and in the same
> window i could change the the dbs in the server.
> In sql2005, I couldn't WHY?

Sorry, I don't understand the question. Can you try again and if you are
getting an error message, please include the exact error message text.

Signature

Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

> Thanks,
> One more question, In sql2000 I can run query analyzer and in the same
[quoted text clipped - 11 lines]
>>
>> Thanks

Reply to this Message

mecn - 30 Mar 2006 22:05 GMT

Sorry,

I mean in sql2000 query analyzer I can do the select statement against diff
dbs. all i need to do is that I click the dropdown box and change the
database and exec the select statement, It's very simple. Now in sql2005. I
need to run a select statement in one db only. no drropdown box to select db
in the same query analyzer window,

Thanks

>> One more question, In sql2000 I can run query analyzer and in the same
>> window i could change the the dbs in the server.
[quoted text clipped - 18 lines]
>>>
>>> Thanks

Reply to this Message

Gail Erickson [MS] - 30 Mar 2006 23:57 GMT

The database dropdown box is in SQL Server Management Studio just like in
Query Analyzer. Maybe you don't have the SQL Editor toolbar open. Try this.
From the toolbar, select View, point to Toolbars and from the list of
toolbars, select SQL Editor.

You can also change the database context by using the USE statement in the
query editor. See the USE (Transact-SQL) topic in Books Online.

USE pubs
GO
SELECT * from authors

USE Northwind
GO
SELECT * FROM Orders

USE AdventureWorks
GO
SELECT * FROM Person.Address

To me, this is an easier method than selecting from the drop-down, but
either way you get the same results.

Regards,

Signature

Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

> Sorry,
>
[quoted text clipped - 27 lines]
>>>>
>>>> Thanks

Reply to this Message