Is there 1 default instance per OS Server or per SQL Server?

On a Server 2k3 x64, I've installed SQL Server 2000 x32 SP4 and SQL Server 2005 x64. When installing both SQL Server 2000 and 2005 on the same server, does each SQL Server have it's own "default instance" or there can only be one "default instance" per server? TIA, Gary.

There can only be one default instance per OS.

Be carefull when installing SQL 2005 on a SQL 2000 machine. It's very easy to upgrade the existing instance without releasing it.

|||Well that explains what happened. I noticed SQL Server 2005 was showing SQL Server 2000 databases. The good thing is it only the test server, not production. But since SQL Server 2000 instance has been updated, I'm assuming I'll have to uninstall both SQL Server 2000 and 2005 in order to set both up correctly? Is there a shortcut to fix this?|||

Correct, there is no way to revert to DB's back to SQL 2000. The default instance naming is the same in both products to make upgrade easier. You can always supply a different instance name with the second install to run both editions side by side.

Thanks,
Sam Lester (MSFT)

sql

Is the default Owner for an Object always 'dbo' ?

In SQL Server 2005, when creating a TABLE, FUNCTION or PROCEDURE, what
governs who is the owner of the object.
Currently, from personal observation, it seems to be always 'dbo',
regardless of the schema that the object is being created in, or who is doing
the creating. Is this true, or are there other factors at play?
With the new seperation of user and schema, is there any advantage to having
objects within a schema owned by different people?
"Al" <Al@.discussions.microsoft.com> wrote in message
news:B6C51168-2ACF-4A70-B324-487C8E6E938C@.microsoft.com...
> In SQL Server 2005, when creating a TABLE, FUNCTION or PROCEDURE, what
> governs who is the owner of the object.
> Currently, from personal observation, it seems to be always 'dbo',
> regardless of the schema that the object is being created in, or who is
> doing
> the creating. Is this true, or are there other factors at play?
>
The default owner of an object is the owner of the schema. Not the user per
se, but "SCHEMA OWNER". If you change the owner of the schema all objects
in that schema owned by SCHEMA OWNER will have their ownership transferred.

> With the new seperation of user and schema, is there any advantage to
> having
> objects within a schema owned by different people?
No. I can't think of a scenario where this would be useful. If you want to
have objects owned by other users, give the user a new schema and make them
the owner of the schema.
David
|||No, the owner of objects is not always dbo by default... I think it
depends on the permission of the user who is creating the object. If
you are a logged in as a "sysadmin" user, then by default all objects
are owned by dbo. I've had a situation here where some developers were
creating objects and the objects were owned by them, not dbo - I'm not
sure what their permissions where however - good question which I will
investiage.
However, you can can have your developers do this, for example, to
ensure ownership is always dbo:
CREATE TABLE dbo.tablename
Al wrote:
> In SQL Server 2005, when creating a TABLE, FUNCTION or PROCEDURE, what
> governs who is the owner of the object.
> Currently, from personal observation, it seems to be always 'dbo',
> regardless of the schema that the object is being created in, or who is doing
> the creating. Is this true, or are there other factors at play?
> With the new seperation of user and schema, is there any advantage to having
> objects within a schema owned by different people?
|||<tootsuite@.gmail.com> wrote in message
news:1161621399.913644.324130@.m73g2000cwd.googlegr oups.com...
> No, the owner of objects is not always dbo by default... I think it
> depends on the permission of the user who is creating the object. If
> you are a logged in as a "sysadmin" user, then by default all objects
> are owned by dbo. I've had a situation here where some developers were
> creating objects and the objects were owned by them, not dbo - I'm not
> sure what their permissions where however - good question which I will
> investiage.
> However, you can can have your developers do this, for example, to
> ensure ownership is always dbo:
> CREATE TABLE dbo.tablename
>
This applies only to SQL 2000. Not 2005. Read up on user/schema
seperation.
David

Is the default Owner for an Object always 'dbo' ?

In SQL Server 2005, when creating a TABLE, FUNCTION or PROCEDURE, what
governs who is the owner of the object.
Currently, from personal observation, it seems to be always 'dbo',
regardless of the schema that the object is being created in, or who is doin
g
the creating. Is this true, or are there other factors at play?
With the new seperation of user and schema, is there any advantage to having
objects within a schema owned by different people?"Al" <Al@.discussions.microsoft.com> wrote in message
news:B6C51168-2ACF-4A70-B324-487C8E6E938C@.microsoft.com...
> In SQL Server 2005, when creating a TABLE, FUNCTION or PROCEDURE, what
> governs who is the owner of the object.
> Currently, from personal observation, it seems to be always 'dbo',
> regardless of the schema that the object is being created in, or who is
> doing
> the creating. Is this true, or are there other factors at play?
>
The default owner of an object is the owner of the schema. Not the user per
se, but "SCHEMA OWNER". If you change the owner of the schema all objects
in that schema owned by SCHEMA OWNER will have their ownership transferred.

> With the new seperation of user and schema, is there any advantage to
> having
> objects within a schema owned by different people?
No. I can't think of a scenario where this would be useful. If you want to
have objects owned by other users, give the user a new schema and make them
the owner of the schema.
David|||No, the owner of objects is not always dbo by default... I think it
depends on the permission of the user who is creating the object. If
you are a logged in as a "sysadmin" user, then by default all objects
are owned by dbo. I've had a situation here where some developers were
creating objects and the objects were owned by them, not dbo - I'm not
sure what their permissions where however - good question which I will
investiage.
However, you can can have your developers do this, for example, to
ensure ownership is always dbo:
CREATE TABLE dbo.tablename
Al wrote:
> In SQL Server 2005, when creating a TABLE, FUNCTION or PROCEDURE, what
> governs who is the owner of the object.
> Currently, from personal observation, it seems to be always 'dbo',
> regardless of the schema that the object is being created in, or who is do
ing
> the creating. Is this true, or are there other factors at play?
> With the new seperation of user and schema, is there any advantage to havi
ng
> objects within a schema owned by different people?|||<tootsuite@.gmail.com> wrote in message
news:1161621399.913644.324130@.m73g2000cwd.googlegroups.com...
> No, the owner of objects is not always dbo by default... I think it
> depends on the permission of the user who is creating the object. If
> you are a logged in as a "sysadmin" user, then by default all objects
> are owned by dbo. I've had a situation here where some developers were
> creating objects and the objects were owned by them, not dbo - I'm not
> sure what their permissions where however - good question which I will
> investiage.
> However, you can can have your developers do this, for example, to
> ensure ownership is always dbo:
> CREATE TABLE dbo.tablename
>
This applies only to SQL 2000. Not 2005. Read up on user/schema
seperation.
David

Is the default Owner for an Object always 'dbo' ?

In SQL Server 2005, when creating a TABLE, FUNCTION or PROCEDURE, what
governs who is the owner of the object.
Currently, from personal observation, it seems to be always 'dbo',
regardless of the schema that the object is being created in, or who is doing
the creating. Is this true, or are there other factors at play?
With the new seperation of user and schema, is there any advantage to having
objects within a schema owned by different people?"Al" <Al@.discussions.microsoft.com> wrote in message
news:B6C51168-2ACF-4A70-B324-487C8E6E938C@.microsoft.com...
> In SQL Server 2005, when creating a TABLE, FUNCTION or PROCEDURE, what
> governs who is the owner of the object.
> Currently, from personal observation, it seems to be always 'dbo',
> regardless of the schema that the object is being created in, or who is
> doing
> the creating. Is this true, or are there other factors at play?
>
The default owner of an object is the owner of the schema. Not the user per
se, but "SCHEMA OWNER". If you change the owner of the schema all objects
in that schema owned by SCHEMA OWNER will have their ownership transferred.
> With the new seperation of user and schema, is there any advantage to
> having
> objects within a schema owned by different people?
No. I can't think of a scenario where this would be useful. If you want to
have objects owned by other users, give the user a new schema and make them
the owner of the schema.
David|||No, the owner of objects is not always dbo by default... I think it
depends on the permission of the user who is creating the object. If
you are a logged in as a "sysadmin" user, then by default all objects
are owned by dbo. I've had a situation here where some developers were
creating objects and the objects were owned by them, not dbo - I'm not
sure what their permissions where however - good question which I will
investiage.
However, you can can have your developers do this, for example, to
ensure ownership is always dbo:
CREATE TABLE dbo.tablename
Al wrote:
> In SQL Server 2005, when creating a TABLE, FUNCTION or PROCEDURE, what
> governs who is the owner of the object.
> Currently, from personal observation, it seems to be always 'dbo',
> regardless of the schema that the object is being created in, or who is doing
> the creating. Is this true, or are there other factors at play?
> With the new seperation of user and schema, is there any advantage to having
> objects within a schema owned by different people?|||<tootsuite@.gmail.com> wrote in message
news:1161621399.913644.324130@.m73g2000cwd.googlegroups.com...
> No, the owner of objects is not always dbo by default... I think it
> depends on the permission of the user who is creating the object. If
> you are a logged in as a "sysadmin" user, then by default all objects
> are owned by dbo. I've had a situation here where some developers were
> creating objects and the objects were owned by them, not dbo - I'm not
> sure what their permissions where however - good question which I will
> investiage.
> However, you can can have your developers do this, for example, to
> ensure ownership is always dbo:
> CREATE TABLE dbo.tablename
>
This applies only to SQL 2000. Not 2005. Read up on user/schema
seperation.
Davidsql

Is SQL Express 2005 by default a single connection db?

Is SQL Express 2005 by default a single connection db?
Background:
I wrote a simple .bat file which executes the following sqlcmd call:
"sqlcmd -S BLUEBOX\SQLEXPRESS -d C:\App_Data\ASPNETDB.MDF -U
MyUserCount -P MyUserPassword -Q "Exec SendMessage" -o C:\Vocalico
\SendMailQuePump\Undelievered.txt"
the above command works fine, as long as no other application is
accessing the db.
Also, using VS2005 I wrote a simple web based calendar scheduling
application which I am testing by launching it via Visual Studio 2005
(using the built in ASP.Net Development Server), the web application
seems to work correctly as well.
The Problem Found:
When I run the VS2005 web application the sqlcmd fails to login (note
that the web application and the sqlcmd access the same db (C:\App_Data
\ASPNETDB.MDF)). The following is the error I get form SQL 2005
Express when running the sqlcmd while the web application is running:
Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
Unable to open the physical file "C:\App_Data\aspnetdb.mdf". Operating
system error 32: "32(The process cannot access the file because it is
being used by another process.)".
Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
Unable to open the physical file "C:\App_Data\aspnetdb_log.ldf".
Operating system error 32: "32(The process cannot access the file
because it is being used by another process.)".
File activation failure. The physical file name "C:\App_Data
\aspnetdb_log.ldf" may be incorrect.
Msg 4060, Level 11, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
Cannot open database "C:\App_Data\ASPNETDB.MDF" requested by the
login. The login failed.
Msg 18456, Level 14, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
Login failed for user 'MyUserCount'.
Questions:
Shouldn't SQL 2005 Express allow more than one user to connect and
authenticate to the db by default? Are there any settings I need to
modify in Visual Studio 2005 or SQL Express 2005 in order to allow my
web app and the sqlcmd command to co-exist and execute without
interfering with each other?
My Setup:
VS 2005
XP Professional SP2
SQL 2005 Express
web.config connection string looks like this:
<add name="ASPNETDBConnectionString" connectionString="Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated
Security=SSPI;User Instance=True" providerName="System.Data.SqlClient"/
>
I have been going around in circles on this issue, any help would be
greatly appreciated!
Sincerely,
-ralphI think there might be some confusion to what the database concept is. For the "real" SQL server, we
create the database and to use it, se specify the database name in the USE command, or the database
name in the connection string or for the -d option for sqlcmd. But you use the physical name of the
mdf file in your sqlcmd call.
My guess is that sqlcmd does either some form of special attach (read about sp_attach_db for the
concept of attach) and it becomes in some form of single user mode. Or that sqlcmd even handles the
instance as a user-instance (a concept only available for Express, which I haven't played with but
which you should be able to find some info if you Google).
So, what I would do is to attach the database (sp_attach_db) and then specify the database name (not
file name) in sqlcmd and your connection string.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RGF" <Raf.Figueroa@.gmail.com> wrote in message
news:dcdeef9a-382d-47dd-9829-b491eb48df6a@.e4g2000hsg.googlegroups.com...
> Is SQL Express 2005 by default a single connection db?
> Background:
> I wrote a simple .bat file which executes the following sqlcmd call:
> "sqlcmd -S BLUEBOX\SQLEXPRESS -d C:\App_Data\ASPNETDB.MDF -U
> MyUserCount -P MyUserPassword -Q "Exec SendMessage" -o C:\Vocalico
> \SendMailQuePump\Undelievered.txt"
> the above command works fine, as long as no other application is
> accessing the db.
> Also, using VS2005 I wrote a simple web based calendar scheduling
> application which I am testing by launching it via Visual Studio 2005
> (using the built in ASP.Net Development Server), the web application
> seems to work correctly as well.
> The Problem Found:
> When I run the VS2005 web application the sqlcmd fails to login (note
> that the web application and the sqlcmd access the same db (C:\App_Data
> \ASPNETDB.MDF)). The following is the error I get form SQL 2005
> Express when running the sqlcmd while the web application is running:
> Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
> Unable to open the physical file "C:\App_Data\aspnetdb.mdf". Operating
> system error 32: "32(The process cannot access the file because it is
> being used by another process.)".
> Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
> Unable to open the physical file "C:\App_Data\aspnetdb_log.ldf".
> Operating system error 32: "32(The process cannot access the file
> because it is being used by another process.)".
> File activation failure. The physical file name "C:\App_Data
> \aspnetdb_log.ldf" may be incorrect.
> Msg 4060, Level 11, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
> Cannot open database "C:\App_Data\ASPNETDB.MDF" requested by the
> login. The login failed.
> Msg 18456, Level 14, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
> Login failed for user 'MyUserCount'.
> Questions:
> Shouldn't SQL 2005 Express allow more than one user to connect and
> authenticate to the db by default? Are there any settings I need to
> modify in Visual Studio 2005 or SQL Express 2005 in order to allow my
> web app and the sqlcmd command to co-exist and execute without
> interfering with each other?
> My Setup:
> VS 2005
> XP Professional SP2
> SQL 2005 Express
> web.config connection string looks like this:
> <add name="ASPNETDBConnectionString" connectionString="Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated
> Security=SSPI;User Instance=True" providerName="System.Data.SqlClient"/
> I have been going around in circles on this issue, any help would be
> greatly appreciated!
> Sincerely,
> -ralph
>
>
>

Is SQL Express 2005 by default a single connection db?

Is SQL Express 2005 by default a single connection db?
Background:
I wrote a simple .bat file which executes the following sqlcmd call:
"sqlcmd -S BLUEBOX\SQLEXPRESS -d C:\App_Data\ASPNETDB.MDF -U
MyUserCount -P MyUserPassword -Q "Exec SendMessage" -o C:\Vocalico
\SendMailQuePump\Undelievered.txt"
the above command works fine, as long as no other application is
accessing the db.
Also, using VS2005 I wrote a simple web based calendar scheduling
application which I am testing by launching it via Visual Studio 2005
(using the built in ASP.Net Development Server), the web application
seems to work correctly as well.
The Problem Found:
When I run the VS2005 web application the sqlcmd fails to login (note
that the web application and the sqlcmd access the same db (C:\App_Data
\ASPNETDB.MDF)). The following is the error I get form SQL 2005
Express when running the sqlcmd while the web application is running:
Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
Unable to open the physical file "C:\App_Data\aspnetdb.mdf". Operating
system error 32: "32(The process cannot access the file because it is
being used by another process.)".
Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
Unable to open the physical file "C:\App_Data\aspnetdb_log.ldf".
Operating system error 32: "32(The process cannot access the file
because it is being used by another process.)".
File activation failure. The physical file name "C:\App_Data
\aspnetdb_log.ldf" may be incorrect.
Msg 4060, Level 11, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
Cannot open database "C:\App_Data\ASPNETDB.MDF" requested by the
login. The login failed.
Msg 18456, Level 14, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
Login failed for user 'MyUserCount'.
Questions:
Shouldn't SQL 2005 Express allow more than one user to connect and
authenticate to the db by default? Are there any settings I need to
modify in Visual Studio 2005 or SQL Express 2005 in order to allow my
web app and the sqlcmd command to co-exist and execute without
interfering with each other?
My Setup:
VS 2005
XP Professional SP2
SQL 2005 Express
web.config connection string looks like this:
<add name="ASPNETDBConnectionString" connectionString="Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\ASPNETDB.MDF;Integrated
Security=SSPI;User Instance=True" providerName="System.Data.SqlClient"/
>
I have been going around in circles on this issue, any help would be
greatly appreciated!
Sincerely,
-ralphI think there might be some confusion to what the database concept is. For t
he "real" SQL server, we
create the database and to use it, se specify the database name in the USE c
ommand, or the database
name in the connection string or for the -d option for sqlcmd. But you use t
he physical name of the
mdf file in your sqlcmd call.
My guess is that sqlcmd does either some form of special attach (read about
sp_attach_db for the
concept of attach) and it becomes in some form of single user mode. Or that
sqlcmd even handles the
instance as a user-instance (a concept only available for Express, which I h
aven't played with but
which you should be able to find some info if you Google).
So, what I would do is to attach the database (sp_attach_db) and then specif
y the database name (not
file name) in sqlcmd and your connection string.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RGF" <Raf.Figueroa@.gmail.com> wrote in message
news:dcdeef9a-382d-47dd-9829-b491eb48df6a@.e4g2000hsg.googlegroups.com...
> Is SQL Express 2005 by default a single connection db?
> Background:
> I wrote a simple .bat file which executes the following sqlcmd call:
> "sqlcmd -S BLUEBOX\SQLEXPRESS -d C:\App_Data\ASPNETDB.MDF -U
> MyUserCount -P MyUserPassword -Q "Exec SendMessage" -o C:\Vocalico
> \SendMailQuePump\Undelievered.txt"
> the above command works fine, as long as no other application is
> accessing the db.
> Also, using VS2005 I wrote a simple web based calendar scheduling
> application which I am testing by launching it via Visual Studio 2005
> (using the built in ASP.Net Development Server), the web application
> seems to work correctly as well.
> The Problem Found:
> When I run the VS2005 web application the sqlcmd fails to login (note
> that the web application and the sqlcmd access the same db (C:\App_Data
> \ASPNETDB.MDF)). The following is the error I get form SQL 2005
> Express when running the sqlcmd while the web application is running:
> Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
> Unable to open the physical file "C:\App_Data\aspnetdb.mdf". Operating
> system error 32: "32(The process cannot access the file because it is
> being used by another process.)".
> Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
> Unable to open the physical file "C:\App_Data\aspnetdb_log.ldf".
> Operating system error 32: "32(The process cannot access the file
> because it is being used by another process.)".
> File activation failure. The physical file name "C:\App_Data
> \aspnetdb_log.ldf" may be incorrect.
> Msg 4060, Level 11, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
> Cannot open database "C:\App_Data\ASPNETDB.MDF" requested by the
> login. The login failed.
> Msg 18456, Level 14, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
> Login failed for user 'MyUserCount'.
> Questions:
> Shouldn't SQL 2005 Express allow more than one user to connect and
> authenticate to the db by default? Are there any settings I need to
> modify in Visual Studio 2005 or SQL Express 2005 in order to allow my
> web app and the sqlcmd command to co-exist and execute without
> interfering with each other?
> My Setup:
> VS 2005
> XP Professional SP2
> SQL 2005 Express
> web.config connection string looks like this:
> <add name="ASPNETDBConnectionString" connectionString="Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\ASPNETDB.MDF;Integrated
> Security=SSPI;User Instance=True" providerName="System.Data.SqlClient"/
> I have been going around in circles on this issue, any help would be
> greatly appreciated!
> Sincerely,
> -ralph
>
>
>