Default Database?

I got the following after trying to connect to SQLEXPRESS
from a remote computer:
Code:
SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
1> select * from users
1> go
MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
Invalid object name 'Users'
and, after querying the tables in the DB, I find that I am in
"maser", not my intended DB. I have changed (what I thought was) the
relevant Logins to "my_db" from "master", but I still get this error.
The Logins I have changed from master to my intended database are:
"sa"
"(servername)/aspnet"
"builtin/users"
"sqlserver2005ms..."
What am i doing wrong?
Thanks!> SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
-E means you login through your windows account. You need to change default database for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>I got the following after trying to connect to SQLEXPRESS
> from a remote computer:
> Code:
> SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> 1> select * from users
> 1> go
> MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> Invalid object name 'Users'
>
> and, after querying the tables in the DB, I find that I am in
> "maser", not my intended DB. I have changed (what I thought was) the
> relevant Logins to "my_db" from "master", but I still get this error.
> The Logins I have changed from master to my intended database are:
> "sa"
> "(servername)/aspnet"
> "builtin/users"
> "sqlserver2005ms..."
> What am i doing wrong?
> Thanks!
>|||Thanks.
Even when I take out " -E " the same thing happens. I get the Master
database.
Could you (somebody) kindly tell me what I need to do to change the
default DB
to my intended DB?
Thanks again.
Tibor Karaszi wote:
> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> -E means you login through your windows account. You need to change default database for this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
> >I got the following after trying to connect to SQLEXPRESS
> > from a remote computer:
> >
> > Code:
> >
> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> > 1> select * from users
> > 1> go
> >
> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> > Invalid object name 'Users'
> >
> >
> >
> > and, after querying the tables in the DB, I find that I am in
> > "maser", not my intended DB. I have changed (what I thought was) the
> > relevant Logins to "my_db" from "master", but I still get this error.
> > The Logins I have changed from master to my intended database are:
> >
> > "sa"
> > "(servername)/aspnet"
> > "builtin/users"
> > "sqlserver2005ms..."
> >
> > What am i doing wrong?
> > Thanks!
> >|||When you take out the -E, you are still using -E (since that is the
default).
So, you still need to set sp_defaultdb for your windows login.
Or, use SQL authentication by specifying a username and password, and set
sp_defaultdb for that login.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177092683.312878.324120@.b58g2000hsg.googlegroups.com...
> Thanks.
> Even when I take out " -E " the same thing happens. I get the Master
> database.
> Could you (somebody) kindly tell me what I need to do to change the
> default DB
> to my intended DB?
> Thanks again.
> Tibor Karaszi wote:
>> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
>> -E means you login through your windows account. You need to change
>> default database for this.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "pbd22" <dushkin@.gmail.com> wrote in message
>> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>> >I got the following after trying to connect to SQLEXPRESS
>> > from a remote computer:
>> >
>> > Code:
>> >
>> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
>> > 1> select * from users
>> > 1> go
>> >
>> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
>> > Invalid object name 'Users'
>> >
>> >
>> >
>> > and, after querying the tables in the DB, I find that I am in
>> > "maser", not my intended DB. I have changed (what I thought was) the
>> > relevant Logins to "my_db" from "master", but I still get this error.
>> > The Logins I have changed from master to my intended database are:
>> >
>> > "sa"
>> > "(servername)/aspnet"
>> > "builtin/users"
>> > "sqlserver2005ms..."
>> >
>> > What am i doing wrong?
>> > Thanks!
>> >
>|||> The Logins I have changed from master to my intended database are:
> "sa"
I strongly recommend you change the default database for 'sa' back to
master:
ALTER LOGIN sa
WITH DEFAULT_DATABASE = master;
Note that ALTER LOGIN is an alternative to the sp_defaultdb method Aaron
mentioned. Also, you can specify an alternative database context using
SQLCMD -d command-line argument rather than relying on the login's default
database:
SQLCMD -d MyDatabase -E -S 192.168.0.10\SQLEXPRESS,2708
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>I got the following after trying to connect to SQLEXPRESS
> from a remote computer:
> Code:
> SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> 1> select * from users
> 1> go
> MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> Invalid object name 'Users'
>
> and, after querying the tables in the DB, I find that I am in
> "maser", not my intended DB. I have changed (what I thought was) the
> relevant Logins to "my_db" from "master", but I still get this error.
> The Logins I have changed from master to my intended database are:
> "sa"
> "(servername)/aspnet"
> "builtin/users"
> "sqlserver2005ms..."
> What am i doing wrong?
> Thanks!
>|||Dan -
Thank you. I appreciate your advice.
I know how to access my default database.
I didn't know the command you suggested, but
it seems to be the same as using "use MyDefaultDB"
on the command line.
The problem is, I want "MyDefaultDB" to be the default
database when I connect from VisualWebDeveloper and
right now VisualWebDeveloper is telling me that the user
doesn't exist when he tries to log on which means that
it is searching for my USERS table in the master table.
So:
> I strongly recommend you change the default database
> for 'sa' back to master:
How can I do what you are saying and allow my users to
login?
Thanks.
Dan Guzman ote-wray:
> > The Logins I have changed from master to my intended database are:
> >
> > "sa"
> I strongly recommend you change the default database for 'sa' back to
> master:
> ALTER LOGIN sa
> WITH DEFAULT_DATABASE = master;
> Note that ALTER LOGIN is an alternative to the sp_defaultdb method Aaron
> mentioned. Also, you can specify an alternative database context using
> SQLCMD -d command-line argument rather than relying on the login's default
> database:
> SQLCMD -d MyDatabase -E -S 192.168.0.10\SQLEXPRESS,2708
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
> >I got the following after trying to connect to SQLEXPRESS
> > from a remote computer:
> >
> > Code:
> >
> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> > 1> select * from users
> > 1> go
> >
> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> > Invalid object name 'Users'
> >
> >
> >
> > and, after querying the tables in the DB, I find that I am in
> > "maser", not my intended DB. I have changed (what I thought was) the
> > relevant Logins to "my_db" from "master", but I still get this error.
> > The Logins I have changed from master to my intended database are:
> >
> > "sa"
> > "(servername)/aspnet"
> > "builtin/users"
> > "sqlserver2005ms..."
> >
> > What am i doing wrong?
> > Thanks!
> >|||> The problem is, I want "MyDefaultDB" to be the default
> database when I connect from VisualWebDeveloper and
> right now VisualWebDeveloper is telling me that the user
> doesn't exist when he tries to log on which means that
> it is searching for my USERS table in the master table.
Just to be clear, are you getting the "Invalid object name 'Users'" message
in VisualWebDeveloper? Perhaps your VisualWebDeveloper connection is
specifying server or database context other than the one desired. Another
possible cause for the invalid object name error is that the object is not
in your default schema. The Best Practice is to schema-qualify object names
(e.g. SELECT * FROM dbo.Users).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177170707.388662.126830@.b58g2000hsg.googlegroups.com...
> Dan -
> Thank you. I appreciate your advice.
> I know how to access my default database.
> I didn't know the command you suggested, but
> it seems to be the same as using "use MyDefaultDB"
> on the command line.
> The problem is, I want "MyDefaultDB" to be the default
> database when I connect from VisualWebDeveloper and
> right now VisualWebDeveloper is telling me that the user
> doesn't exist when he tries to log on which means that
> it is searching for my USERS table in the master table.
> So:
>> I strongly recommend you change the default database
>> for 'sa' back to master:
> How can I do what you are saying and allow my users to
> login?
> Thanks.
> Dan Guzman ote-wray:
>> > The Logins I have changed from master to my intended database are:
>> >
>> > "sa"
>> I strongly recommend you change the default database for 'sa' back to
>> master:
>> ALTER LOGIN sa
>> WITH DEFAULT_DATABASE = master;
>> Note that ALTER LOGIN is an alternative to the sp_defaultdb method Aaron
>> mentioned. Also, you can specify an alternative database context using
>> SQLCMD -d command-line argument rather than relying on the login's
>> default
>> database:
>> SQLCMD -d MyDatabase -E -S 192.168.0.10\SQLEXPRESS,2708
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "pbd22" <dushkin@.gmail.com> wrote in message
>> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>> >I got the following after trying to connect to SQLEXPRESS
>> > from a remote computer:
>> >
>> > Code:
>> >
>> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
>> > 1> select * from users
>> > 1> go
>> >
>> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
>> > Invalid object name 'Users'
>> >
>> >
>> >
>> > and, after querying the tables in the DB, I find that I am in
>> > "maser", not my intended DB. I have changed (what I thought was) the
>> > relevant Logins to "my_db" from "master", but I still get this error.
>> > The Logins I have changed from master to my intended database are:
>> >
>> > "sa"
>> > "(servername)/aspnet"
>> > "builtin/users"
>> > "sqlserver2005ms..."
>> >
>> > What am i doing wrong?
>> > Thanks!
>> >
>|||> I didn't know the command you suggested, but
> it seems to be the same as using "use MyDefaultDB"
> on the command line.
No, it is not. If your user has a default database of 'foo' but the user
cannot access foo, they won't have a chance to say 'use bar' because they
won't be able to log in. It depends on the tool you are using, but this is
the case in many tools...|||Dan -
Thanks for your reply. I thought I had fixed this (hence the delay)
but the error
has returned.
> Just to be clear, are you getting the "Invalid object name 'Users'" message
> in VisualWebDeveloper?
I "am" getting the "Invalid object name "Users" " error when I
try to connect using the SQLCMD. It is obvious that the command is
trying to
validate my login using the master DB and there is no Users table
there.
My connection string looks like the following:
<add name="myConnectionString" connectionString="Data
Source=192.168.0.10\SQLEXPRESS,2708;Initial Catalog=Trezoro;
Integrated Security=True; uid=sa; pwd=mypassword;"
providerName="System.Data.SqlClient"/>
I am using the 'sa' account for access and, if the 'sa' default
database is master
I am still a bit lost as to how to validate against a table that isn't
in the master DB.
> Another possible cause for the invalid object name error is that the object is
> not in your default schema. The Best Practice is to schema-qualify object
names
> (e.g. SELECT * FROM dbo.Users).
When I am in the database, I can use select * from users or select *
from dbo.users - they both work.
This is taking me a while but - how do i change the default database
for when
my users login?|||> trying to
> validate my login using the master DB and there is no Users table
> there.
I think you're confusing a users table that you created with SQL Server's
internal tables for maintaining login and user information.
> This is taking me a while but - how do i change the default database
> for when
> my users login?
Take a look at sp_defaultdb in Books Online.|||> This is taking me a while but - how do i change the default database
> for when
> my users login?
As Aaron mentioned, you can specify the default database for a login with
sp_default_db. Since you are using SQL 2005, you can alternatively use DCL:
ALTER LOGIN MyLogin
WITH DEFAULT_DATABASE = MyLogin;
However, the login's default database is used only if you do not specify a
database context when connecting. Try this, which I would expect this to
succeed as long as your Windows account has access to the Trezoro database:
SQLCMD -E -S -d Trezoro 192.168.0.10\SQLEXPRESS,2708 - Q"SELECT * FROM
dbo.users"
GO
If the above failes with error "invalid object name dbo.users", run the
following to verify database and user context:
SQLCMD -E -S -d Trezoro 192.168.0.10\SQLEXPRESS,2708 - Q"SELECT DB_NAME(),
USER"
If the database and user are as expected, verify the table actually exists:
SQLCMD -E -S -d Trezoro 192.168.0.10\SQLEXPRESS,2708 - Q"SELECT name FROM
sys.tables WHERE name = 'users'"
> <add name="myConnectionString" connectionString="Data
> Source=192.168.0.10\SQLEXPRESS,2708;Initial Catalog=Trezoro;
> Integrated Security=True; uid=sa; pwd=mypassword;"
> providerName="System.Data.SqlClient"/>
> I am using the 'sa' account for access and, if the 'sa' default
> database is master
> I am still a bit lost as to how to validate against a table that isn't
> in the master DB.
Because you specified 'Integrated Security=True' in the connection string,
the user and password specification are ignored. The connection is instead
done under the context of a Windows account. I'm not an web guy but I
believe your Windows account is used when you run from the IDE. This
doesn't explain why you would get the invalid object error, though. I would
first troubleshoot the problem with SQLCMD before you try the application.
In any case, you should never use 'sa' for routine development or
application access.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177879467.944332.115100@.n59g2000hsh.googlegroups.com...
> Dan -
> Thanks for your reply. I thought I had fixed this (hence the delay)
> but the error
> has returned.
>> Just to be clear, are you getting the "Invalid object name 'Users'"
>> message
>> in VisualWebDeveloper?
> I "am" getting the "Invalid object name "Users" " error when I
> try to connect using the SQLCMD. It is obvious that the command is
> trying to
> validate my login using the master DB and there is no Users table
> there.
> My connection string looks like the following:
> <add name="myConnectionString" connectionString="Data
> Source=192.168.0.10\SQLEXPRESS,2708;Initial Catalog=Trezoro;
> Integrated Security=True; uid=sa; pwd=mypassword;"
> providerName="System.Data.SqlClient"/>
> I am using the 'sa' account for access and, if the 'sa' default
> database is master
> I am still a bit lost as to how to validate against a table that isn't
> in the master DB.
>> Another possible cause for the invalid object name error is that the
>> object is
>> not in your default schema. The Best Practice is to schema-qualify
>> object
> names
>> (e.g. SELECT * FROM dbo.Users).
> When I am in the database, I can use select * from users or select *
> from dbo.users - they both work.
> This is taking me a while but - how do i change the default database
> for when
> my users login?
>sql