applying dbo to all objects by default?

Hi,
Is there a way to assign all objects being created to be assigned to DBO as
opposed to the person who is creating them?
We have some programmers who need db_owner access, and when they create a
new sp or table the objects are tied to them now. Can it by defaul be
assigned to DBO instead?
TIA,
ScottScott
They should specify a DBO user as
CREATE TABLE dbo.Test (col INT) and then the table will belong to the DBO
user
If you want the DBO to be created by default you should add them to
sysadmin server role
"Scott" <sfruhwirth@.gmail.com> wrote in message
news:OqYCpMXUFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there a way to assign all objects being created to be assigned to DBO
as
> opposed to the person who is creating them?
> We have some programmers who need db_owner access, and when they create a
> new sp or table the objects are tied to them now. Can it by defaul be
> assigned to DBO instead?
> TIA,
> Scott
>

Application Settings and data extensions

I have application settings that I set in a configuration class and expose
via properties. My business objects use these settings. I use these
business objects in a data extension for reporting services. I include the
configuration dll and business object dlls with my data extension dll. The
application settings are not being found. How do I get these to work?Hello Bruce,
Based on my research, you need to check whether you have include your
configuration dll in your ReportServer web.config file.
Please try to check you web.config file.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I don't understand your response. Could you please provide more information.
"Wei Lu [MSFT]" wrote:
> Hello Bruce,
> Based on my research, you need to check whether you have include your
> configuration dll in your ReportServer web.config file.
> Please try to check you web.config file.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Bruce,
First, I would like to know have you use the strong named assembly?
If so, I will suggest you to put all the assembly to the GAC.
If not strong named assembly, please put all the assembly to the Bin folder
of your extension project.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Application Role permissions report

Can I generate the report for each application role with their objects &
their permissions using sql stored procedure ?
Let me know.
Thanks for all the help.
Manoj
Answered your question in another thread.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:1FF02616-9454-47DC-951D-14586999778F@.microsoft.com...
> Can I generate the report for each application role with their objects &
> their permissions using sql stored procedure ?
> Let me know.
> Thanks for all the help.
> Manoj

Application Role permissions report

Can I generate the report for each application role with their objects &
their permissions using sql stored procedure ?
Let me know.
Thanks for all the help.
ManojAnswered your question in another thread.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:1FF02616-9454-47DC-951D-14586999778F@.microsoft.com...
> Can I generate the report for each application role with their objects &
> their permissions using sql stored procedure ?
> Let me know.
> Thanks for all the help.
> Manoj

Application Role

Hi,
I have an Access 2003 project which uses a "dummy" login for the standard
connection without any rights on objects. After establishing the ADO
connection via VBA code I enable the application role which is then used to
do all database access
cn.Execute "EXEC sp_setapprole 'App_Role', '" & strPW & "'"
This works fine unless I assign a rowsource via a code line like this
...Form.RecordSource = "Exec GET_" & Str_SubFormName & " " & strWhere
This works only when I assign the dummy login the right to execute the
stored procedure. It is not enough to assign this right to the Application
role. But this means that the dummy role has access to this table via the
stored procedure which I wanted to avoid. What do I miss, or is this by
design? Any other solutions for this scenario?
TIA,
Norbert MeissNorbert Meiss (NorbertMeiss@.discussions.microsoft.com) writes:
> I have an Access 2003 project which uses a "dummy" login for the
> standard connection without any rights on objects. After establishing
> the ADO connection via VBA code I enable the application role which is
> then used to do all database access
> cn.Execute "EXEC sp_setapprole 'App_Role', '" & strPW & "'"
> This works fine unless I assign a rowsource via a code line like this
> ...Form.RecordSource = "Exec GET_" & Str_SubFormName & " " & strWhere
> This works only when I assign the dummy login the right to execute the
> stored procedure. It is not enough to assign this right to the Application
> role. But this means that the dummy role has access to this table via the
> stored procedure which I wanted to avoid. What do I miss, or is this by
> design? Any other solutions for this scenario?
Since I don't know Access, I will have to guess a little here. But I have
a feeling that the RecordSource stored procedure gets executed on a second
connection to SQL Server. In such case, that connection will not have the
application role set.
Why you get this second connection, I don't know. It could be inherent
with the Form.RecordSource thing, and thus no way to avoid it. But there
is also an issue with ADO. If you fail to get all data from a query, some
client libraries will tell you that the connection is busy, and no new
query can be submitted. But ADO tries to be a nice guy, and opens a new
connection to SQL Server and submits the qurey on that connection. This
often causes more problem than it solves. Really to avoid this, I don't
know, since I know nothing about your code. But "SET NOCOUNT ON" saves
the day in many cases, since the result sets you have not consumed,
often are rowcounts from INSERT/UPDATE/DELETE statements.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I cannot reproduce the strange behaviour from my first post - maybe I was
wrong with my observations. The SET NOCOUNT ON hint is helpful anyway. I
didn't understand its meaning so far. Thank you.
Norbert Meiss
"Erland Sommarskog" wrote:

> Norbert Meiss (NorbertMeiss@.discussions.microsoft.com) writes:
> Since I don't know Access, I will have to guess a little here. But I have
> a feeling that the RecordSource stored procedure gets executed on a second
> connection to SQL Server. In such case, that connection will not have the
> application role set.
> Why you get this second connection, I don't know. It could be inherent
> with the Form.RecordSource thing, and thus no way to avoid it. But there
> is also an issue with ADO. If you fail to get all data from a query, some
> client libraries will tell you that the connection is busy, and no new
> query can be submitted. But ADO tries to be a nice guy, and opens a new
> connection to SQL Server and submits the qurey on that connection. This
> often causes more problem than it solves. Really to avoid this, I don't
> know, since I know nothing about your code. But "SET NOCOUNT ON" saves
> the day in many cases, since the result sets you have not consumed,
> often are rowcounts from INSERT/UPDATE/DELETE statements.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||In addition to what Erland said, you are going to have intermittent
problems using application roles with Access unless you turn off
connection pooling in the connection string and test to make sure that
the application role is active before you execute any EXEC statements.
This is all going to add overhead to the app and slow it down somewhat
-- how much depends on the app and number of users. It is generally
recommended NOT to use application roles with Access because it opens
and closes connections under the covers. See
http://support.microsoft.com/defaul...B;EN-US;Q229564 for
more info on the connection pooling issue.
--Mary
On Mon, 27 Dec 2004 00:19:03 -0800, "Norbert Meiss"
<NorbertMeiss@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I cannot reproduce the strange behaviour from my first post - maybe I was
>wrong with my observations. The SET NOCOUNT ON hint is helpful anyway. I
>didn't understand its meaning so far. Thank you.
>Norbert Meiss
>"Erland Sommarskog" wrote:
>|||Thanks Mary.
While performance is not an issue so far with some 25 users and a big server
I will give it a second thougt and some testing.
Norbert
"Mary Chipman" wrote:

> In addition to what Erland said, you are going to have intermittent
> problems using application roles with Access unless you turn off
> connection pooling in the connection string and test to make sure that
> the application role is active before you execute any EXEC statements.
> This is all going to add overhead to the app and slow it down somewhat
> -- how much depends on the app and number of users. It is generally
> recommended NOT to use application roles with Access because it opens
> and closes connections under the covers. See
> http://support.microsoft.com/defaul...B;EN-US;Q229564 for
> more info on the connection pooling issue.
> --Mary
> On Mon, 27 Dec 2004 00:19:03 -0800, "Norbert Meiss"
> <NorbertMeiss@.discussions.microsoft.com> wrote:
>
>|||A good tool to use when testing is Profiler, which allows you to
eavesdrop on the actual conversation Access is having with the SQL
Server. It's interesting to see what's going on under the covers when
it comes to troubleshooting existing problems and avoiding potential
ones.
--Mary
On Mon, 27 Dec 2004 06:39:04 -0800, "Norbert Meiss"
<NorbertMeiss@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Mary.
>While performance is not an issue so far with some 25 users and a big serve
r
>I will give it a second thougt and some testing.
>Norbert
>"Mary Chipman" wrote:
>

application login permissions...

background sql2kt on nt5
if I created a sql standard as application login. should i assign
permissions of objects to that accout or should i create a role, assign
permissions of objects to the role, then add that application login account
to the role?
what are some of the common or best practices? can anyone give me some
suggestions? thank you.
steveHi,
I suggest you to create a role and assign permissions to that role.
Say if it is a Finance application, Create a new user role called Finance
and aiisn all permissions to that role. After this you can assign this role
to the application user. If there a new application user comes in later, you
can just assign that role to that user.
(Before creating the role, Just have a look into db_datareader and
db_datawriter role- this may help you out)
Thanks
Hari
MCDBA
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:#yfq2fuNEHA.1160@.TK2MSFTNGP09.phx.gbl...
> background sql2kt on nt5
> if I created a sql standard as application login. should i assign
> permissions of objects to that accout or should i create a role, assign
> permissions of objects to the role, then add that application login
account
> to the role?
> what are some of the common or best practices? can anyone give me some
> suggestions? thank you.
> steve
>

application login permissions...

background sql2kt on nt5
if I created a sql standard as application login. should i assign
permissions of objects to that accout or should i create a role, assign
permissions of objects to the role, then add that application login account
to the role?
what are some of the common or best practices? can anyone give me some
suggestions? thank you.
steve
Hi,
I suggest you to create a role and assign permissions to that role.
Say if it is a Finance application, Create a new user role called Finance
and aiisn all permissions to that role. After this you can assign this role
to the application user. If there a new application user comes in later, you
can just assign that role to that user.
(Before creating the role, Just have a look into db_datareader and
db_datawriter role- this may help you out)
Thanks
Hari
MCDBA
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:#yfq2fuNEHA.1160@.TK2MSFTNGP09.phx.gbl...
> background sql2kt on nt5
> if I created a sql standard as application login. should i assign
> permissions of objects to that accout or should i create a role, assign
> permissions of objects to the role, then add that application login
account
> to the role?
> what are some of the common or best practices? can anyone give me some
> suggestions? thank you.
> steve
>

application login permissions...

background sql2kt on nt5
if I created a sql standard as application login. should i assign
permissions of objects to that accout or should i create a role, assign
permissions of objects to the role, then add that application login account
to the role?
what are some of the common or best practices? can anyone give me some
suggestions? thank you.
steveHi,
I suggest you to create a role and assign permissions to that role.
Say if it is a Finance application, Create a new user role called Finance
and aiisn all permissions to that role. After this you can assign this role
to the application user. If there a new application user comes in later, you
can just assign that role to that user.
(Before creating the role, Just have a look into db_datareader and
db_datawriter role- this may help you out)
Thanks
Hari
MCDBA
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:#yfq2fuNEHA.1160@.TK2MSFTNGP09.phx.gbl...
> background sql2kt on nt5
> if I created a sql standard as application login. should i assign
> permissions of objects to that accout or should i create a role, assign
> permissions of objects to the role, then add that application login
account
> to the role?
> what are some of the common or best practices? can anyone give me some
> suggestions? thank you.
> steve
>

Append To File - SQL Server 2005

Their appears to be a bug with the "Append to File" script option that is
available when scripting database objects in SQL Server 2005's SSMS. When I
set the "Append to File" option to false it still appends the script output
to what ever was in the file before. I am scripting all of my stored
procedures in a given database to a separate file for each object. The
first time I generate the files for the procs everything looks fine. When I
script them out a second time to the same location with the "Append to File"
option set to false the scripting engine erroneously appends the new script
output to the end of the existing file.
I saw a similar question in an MSDN posting.
What is the status on this problem.
Thanks!
Chris
Are you on the latest service pack? There were a lot of changes associated
with scripting and such in SP2 but I am not sure if that was one of them or
not. If you are on the latest SP I would delete all the files in that folder
first and post a bug on connect.
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:uVGT1d1tHHA.4844@.TK2MSFTNGP04.phx.gbl...
> Their appears to be a bug with the "Append to File" script option that is
> available when scripting database objects in SQL Server 2005's SSMS. When
> I set the "Append to File" option to false it still appends the script
> output to what ever was in the file before. I am scripting all of my
> stored procedures in a given database to a separate file for each object.
> The first time I generate the files for the procs everything looks fine.
> When I script them out a second time to the same location with the "Append
> to File" option set to false the scripting engine erroneously appends the
> new script output to the end of the existing file.
> I saw a similar question in an MSDN posting.
> What is the status on this problem.
> Thanks!
> Chris
>
|||Hi Chris,
As Andrew said, you may check if SQL Server 2005 SP2 had been installed on
your computer.
You can run SELECT @.@.VERSION to check if the version number is equal to or
larger than 9.00.3042 (initial SP2 version).
I performed a test under my SQL Server 2005 SP2 and everything worked fine.
The file will be replaced if I select "Append to File" as false.
If SQL Server 2005 SP2 had not been installed under your computer, please
install it to see if this issue persists. You can download the latest
service packs from:
Microsoft SQL Server 2005 Service Pack 2
http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-
8f0c-63fa18f26d3a&displaylang=en
Microsoft SQL Server 2005 Service Pack 2 issue: Cleanup tasks run at
different intervals than intended
http://support.microsoft.com/kb/933508
Hope this helps. If you have any other questions or concerns, please feel
free to let us know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Anything that you find in SQL object scripts, you can also find them in system tables?

I tried all the INFORMATION_SCHEMA on SQL 2000 and
I see that the system tables hold pretty much everything I am
interested in: Objects names (columns, functions, stored procedures, ...)
stored procedure statements in syscomments table.

My questions are:

If you script your whole database everything you end up having
in the text sql scripts, are those also located in the system tables?
That means i could simply read those system tables to get any information
I would normally look in the sql script files?

Can i quickly generate a SQL statement of all the indexes on my database?

I read many places that Microsoft
says not to modify anything in those tables and not query them since their
structure might change in future SQL versions.

Is it safe to use and rely the system tables?

I basically want to do at least fetching of information i want from the
system tables rather than the SQL script files.
I also want to know if it's pretty safe for me to make changes in these
tables.
Can i rename an object name for example an Index name, a Stored Procedure
name?
Can i add a new column in the syscolumns table for a user table?

Thank youThe SQLDMO script method can be used to script any SQL Server object. You
can also generate scripts from Enterprise, Query Analyzer or from the
command line using the scptxfr.exe utility.

> Is it safe to use and rely the system tables?

Yes and no. The INFORMATION_SCHEMA views are a better source for lots of the
metadata and they are usually recommended as the preferred method whenever
possible. However, the information schema doesn't cover everything (no
indexes for example) so you may still need to make use of system tables for
some things. If you rely only on the features documented in Books Online and
avoid referencing the stuff that isn't explained or that's marked as
"reserved" then you should be fairly safe. Be sensible though and don't use
system tables when you don't have to. Check out the "Meta Data Functions"
topic in Books Online for other alternatives.

In SQL Server 2005 the old system tables are superceded by a new set of
views that give you much more comprehensive and convenient view of the
metadata. The old-style system tables are still supported for backwards
compatibility although they aren't being extended to support new features.
In theory, most things will work in 2005 as in 2000 but it's not going to be
100% so assume some things may need to be fixed if and when you upgrade.

> I also want to know if it's pretty safe for me to make changes in these
> tables.

Never. Not if you value the integrity of your server and your database. All
the things you need to do are supported through procs and DDL statements.
That includes renaming objects and adding new columns. Don't mess with
updates against the system tables.

--
David Portas
SQL Server MVP
--|||>> Is it safe to use and rely the system tables?

> Yes and no. The INFORMATION_SCHEMA views are a better source for lots of
> the metadata and they are usually recommended as the preferred method
> whenever possible. However, the information schema doesn't cover
> everything (no indexes for example) so you may still need to make use of
> system tables for some things. If you rely only on the features documented
> in Books Online and avoid referencing the stuff that isn't explained or
> that's marked as "reserved" then you should be fairly safe. Be sensible
> though and don't use system tables when you don't have to. Check out the
> "Meta Data Functions" topic in Books Online for other alternatives.

I started reading about Meta Data last week in SQL Books Online and I
am interested to learn more. However I can only do one thing at a time so
I am trying to understand the system tables first and find out ways
how/when/why I could and should use them.
I'll check out "Meta Data Functions" to see what they do.

>> I also want to know if it's pretty safe for me to make changes in these
>> tables.

> Never. Not if you value the integrity of your server and your database.
> All the things you need to do are supported through procs and DDL
> statements. That includes renaming objects and adding new columns. Don't
> mess with updates against the system tables.

Understood. I won't make any changes in these tables. You are right if I can
use the supported procs and DDL statements then I'll use them.

Thank you|||>> Is it safe to use and rely the system tables?

> Yes and no. The INFORMATION_SCHEMA views are a better source for lots of
> the metadata and they are usually recommended as the preferred method
> whenever possible. However, the information schema doesn't cover
> everything (no indexes for example) so you may still need to make use of
> system tables for some things. If you rely only on the features documented
> in Books Online and avoid referencing the stuff that isn't explained or
> that's marked as "reserved" then you should be fairly safe. Be sensible
> though and don't use system tables when you don't have to. Check out the
> "Meta Data Functions" topic in Books Online for other alternatives.

I started reading about Meta Data last week in SQL Books Online and I
am interested to learn more. However I can only do one thing at a time so
I am trying to understand the system tables first and find out ways
how/when/why I could and should use them.
I'll check out "Meta Data Functions" to see what they do.

>> I also want to know if it's pretty safe for me to make changes in these
>> tables.

> Never. Not if you value the integrity of your server and your database.
> All the things you need to do are supported through procs and DDL
> statements. That includes renaming objects and adding new columns. Don't
> mess with updates against the system tables.

Understood. I won't make any changes in these tables. You are right if I can
use the supported procs and DDL statements then I'll use them.

Thank you|||serge (sergea@.nospam.ehmail.com) writes:
> I tried all the INFORMATION_SCHEMA on SQL 2000 and
> I see that the system tables hold pretty much everything I am
> interested in: Objects names (columns, functions, stored procedures, ...)
> stored procedure statements in syscomments table.
> My questions are:
> If you script your whole database everything you end up having
> in the text sql scripts, are those also located in the system tables?

Scripting does not place anything in system tables. Creating objects
does. The source code of stored procedures, views, functions, constraints
and a few more objects are in syscomments. However, they are stored in
8000 character slices, and non-trivial to decode.

Tables, indexes and user-defined types are not stored as text as such
in SQL Server, but as scattered pieces which the scripting tools
reassemble.

In any case, you should not really script your databases more than at
most once. View the database as a storage for binary objects, and keep
your source code under version control. (But if you started without
version control, you may need to script once to get yourself a baseline.)

> I read many places that Microsoft
> says not to modify anything in those tables and not query them since their
> structure might change in future SQL versions.
> Is it safe to use and rely the system tables?

As David said, there are new means for retrieving meta data in SQL 2005,
and the old system tables in SQL 2005 become views implemented on top
of these, known as "Compatibility views". They are also marked as
deperecated, and they will surely disappear from a future version of
SQL Server, but this is not likely to happen this decade.

Important is to only use documented columns, and only use columns what
they are documented for. A column which has the short description of
"Reserved", will very likely always have a value of 0 or NULL in SQL 2005.

Personally, I use only the system tables for meta-data access in SQL 2000,
and this is also what I recommend. The INFORMATION_SCHEMA views are not
whole-covering, so mixing both means that you must know two paradigms.
There are also some traps with the views which can cause you to not
get data you expect, or columns may have names which makes promises
they don't live up to. SQL Server MVP Tony Rogerson also liks to point
out that they have scalability problems. Finally, all that uppercase
is ugly. :-)

> I basically want to do at least fetching of information i want from the
> system tables rather than the SQL script files.
> I also want to know if it's pretty safe for me to make changes in these
> tables.

Never make changes directly to system tables, unless you are told to so
by a Microsoft support professional.

> Can i rename an object name for example an Index name, a Stored Procedure
> name?

To rename an index use sp_rename. To rename a stored procedure, drop
the procedure and reload it under the new name.

> Can i add a new column in the syscolumns table for a user table?

Absolutely not! You will corrupt your database.

Please keep in mind that there is a whole lot of internal structures
in SQL Server that are not exposed. When you add a column to a table,
SQL Server may need to update all pages in the tables, to create space
for the column. Query plans for procedures that has SELECT * may need
to be flushed etc.

In fact, in SQL 2005, you don't even have read access to the real system
tables. All you see is views.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp