Default Databases and Windows Groups

Hello!
I have a question about default databases and Windows groups. A Windows
login is a member of two Windows groups. These two Windows groups have been
granted access to a sql server.
Login1 Member of Group1 and Group2
Group1 DefaultDbA
Group2 DefaultDbB
How is the default database determined if Login1 is connecting to the sql
server?
Best regards
Ola HallengrenThis is, to the best of my knowledge, not deterministic. There is not setting for this. So, it could
go either way. IMO, an app should never depend on default database - it should set the database in
the connection string. You could, of course, add Login! as a login to SQL Server and specify the
default database for that login (I'm pretty certain that this would be honored).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
> Hello!
> I have a question about default databases and Windows groups. A Windows
> login is a member of two Windows groups. These two Windows groups have been
> granted access to a sql server.
> Login1 Member of Group1 and Group2
> Group1 DefaultDbA
> Group2 DefaultDbB
> How is the default database determined if Login1 is connecting to the sql
> server?
> Best regards
> Ola Hallengren|||Thanks, Tibor. I agree with you that applications should not depend on
default databases. This is not a problem for us. The problem we have is with
the administrators managing the database servers using the SQL Server client
tools.
Enterprise Manager
If the default database is not accessible you can not log on.
Query Analyzer
If the default database is not accessible you can not log on.
Management Studio
There is an option to specify a database in the connection that enables you
to log on, even if the default database is not accessible. However then the
graphical dialogs is not working.
We're considering going away from using default databases (leaving it to
master on all logins).
/Ola
"Tibor Karaszi" wrote:
> This is, to the best of my knowledge, not deterministic. There is not setting for this. So, it could
> go either way. IMO, an app should never depend on default database - it should set the database in
> the connection string. You could, of course, add Login! as a login to SQL Server and specify the
> default database for that login (I'm pretty certain that this would be honored).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
> > Hello!
> >
> > I have a question about default databases and Windows groups. A Windows
> > login is a member of two Windows groups. These two Windows groups have been
> > granted access to a sql server.
> >
> > Login1 Member of Group1 and Group2
> >
> > Group1 DefaultDbA
> > Group2 DefaultDbB
> >
> > How is the default database determined if Login1 is connecting to the sql
> > server?
> >
> > Best regards
> >
> > Ola Hallengren
>|||> We're considering going away from using default databases (leaving it to
> master on all logins).
This is what I do. And I then let the tool specify the database (/d for QA, in the registered server
for SSMS etc.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:5D4CCE2E-E6BD-48DC-832D-517AB35364A4@.microsoft.com...
> Thanks, Tibor. I agree with you that applications should not depend on
> default databases. This is not a problem for us. The problem we have is with
> the administrators managing the database servers using the SQL Server client
> tools.
> Enterprise Manager
> If the default database is not accessible you can not log on.
> Query Analyzer
> If the default database is not accessible you can not log on.
> Management Studio
> There is an option to specify a database in the connection that enables you
> to log on, even if the default database is not accessible. However then the
> graphical dialogs is not working.
> We're considering going away from using default databases (leaving it to
> master on all logins).
> /Ola
>
> "Tibor Karaszi" wrote:
>> This is, to the best of my knowledge, not deterministic. There is not setting for this. So, it
>> could
>> go either way. IMO, an app should never depend on default database - it should set the database
>> in
>> the connection string. You could, of course, add Login! as a login to SQL Server and specify the
>> default database for that login (I'm pretty certain that this would be honored).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
>> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
>> > Hello!
>> >
>> > I have a question about default databases and Windows groups. A Windows
>> > login is a member of two Windows groups. These two Windows groups have been
>> > granted access to a sql server.
>> >
>> > Login1 Member of Group1 and Group2
>> >
>> > Group1 DefaultDbA
>> > Group2 DefaultDbB
>> >
>> > How is the default database determined if Login1 is connecting to the sql
>> > server?
>> >
>> > Best regards
>> >
>> > Ola Hallengren
>>|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com|||> I'm thinking if there is a way to trace if the applications are specifying a
> database on connection, or if they are depending on the default databases.
Seems like Profiler can show you this. If you capture the "User Error Message" event, you will see a
"Changed database context to dbname" events when you start the app. I guess this is the same as if
you do an explicit USE in the code, but whichever the app uses will make the app independent of the
default database for the login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:6207977A-193E-4FED-832C-E302E37F5BD4@.microsoft.com...
> >IMO, an app should never depend on default database - it should set the
> database in the connection string.
> I'm thinking if there is a way to trace if the applications are specifying a
> database on connection, or if they are depending on the default databases.
> One way is of course to to change the default databases to master and see
> which applications that fails. Is there a more elegant way?
> /Ola Hallengren
>
> "Tibor Karaszi" wrote:
>> > We're considering going away from using default databases (leaving it to
>> > master on all logins).
>> This is what I do. And I then let the tool specify the database (/d for QA, in the registered
>> server
>> for SSMS etc.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
>> news:5D4CCE2E-E6BD-48DC-832D-517AB35364A4@.microsoft.com...
>> > Thanks, Tibor. I agree with you that applications should not depend on
>> > default databases. This is not a problem for us. The problem we have is with
>> > the administrators managing the database servers using the SQL Server client
>> > tools.
>> >
>> > Enterprise Manager
>> > If the default database is not accessible you can not log on.
>> >
>> > Query Analyzer
>> > If the default database is not accessible you can not log on.
>> >
>> > Management Studio
>> > There is an option to specify a database in the connection that enables you
>> > to log on, even if the default database is not accessible. However then the
>> > graphical dialogs is not working.
>> >
>> > We're considering going away from using default databases (leaving it to
>> > master on all logins).
>> >
>> > /Ola
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> This is, to the best of my knowledge, not deterministic. There is not setting for this. So, it
>> >> could
>> >> go either way. IMO, an app should never depend on default database - it should set the
>> >> database
>> >> in
>> >> the connection string. You could, of course, add Login! as a login to SQL Server and specify
>> >> the
>> >> default database for that login (I'm pretty certain that this would be honored).
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
>> >> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
>> >> > Hello!
>> >> >
>> >> > I have a question about default databases and Windows groups. A Windows
>> >> > login is a member of two Windows groups. These two Windows groups have been
>> >> > granted access to a sql server.
>> >> >
>> >> > Login1 Member of Group1 and Group2
>> >> >
>> >> > Group1 DefaultDbA
>> >> > Group2 DefaultDbB
>> >> >
>> >> > How is the default database determined if Login1 is connecting to the sql
>> >> > server?
>> >> >
>> >> > Best regards
>> >> >
>> >> > Ola Hallengren
>> >>
>> >>
>>|||I've done some tests with this and unfortunately it doesn't seem to work.
I have a database called [TestDb] and a login called [test]. The login
[test] has been granted access to the database [TestDb]. [TestDb] is also the
default database.
Scenario 1: Logging in using sqlcmd and not specifying a database (depending
on the default database).
sqlcmd -S Server1 -U test -P test
Profiler shows the User Error Message below.
Changed database context to 'TestDb'.
Scenario 2: Logging in using sqlcmd and specifying a database (not depending
on the default database)
sqlcmd -S Server1 -U test -P test -d TestDb
Profiler shows the User Error Message below.
Changed database context to 'TestDb'.
As I see it I can not determine if a database is specified in the connection
or if it is depending on the default database.
/Ola
"Tibor Karaszi" wrote:
> > I'm thinking if there is a way to trace if the applications are specifying a
> > database on connection, or if they are depending on the default databases.
> Seems like Profiler can show you this. If you capture the "User Error Message" event, you will see a
> "Changed database context to dbname" events when you start the app. I guess this is the same as if
> you do an explicit USE in the code, but whichever the app uses will make the app independent of the
> default database for the login.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:6207977A-193E-4FED-832C-E302E37F5BD4@.microsoft.com...
> > >IMO, an app should never depend on default database - it should set the
> > database in the connection string.
> >
> > I'm thinking if there is a way to trace if the applications are specifying a
> > database on connection, or if they are depending on the default databases.
> > One way is of course to to change the default databases to master and see
> > which applications that fails. Is there a more elegant way?
> >
> > /Ola Hallengren
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > We're considering going away from using default databases (leaving it to
> >> > master on all logins).
> >>
> >> This is what I do. And I then let the tool specify the database (/d for QA, in the registered
> >> server
> >> for SSMS etc.)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> >> news:5D4CCE2E-E6BD-48DC-832D-517AB35364A4@.microsoft.com...
> >> > Thanks, Tibor. I agree with you that applications should not depend on
> >> > default databases. This is not a problem for us. The problem we have is with
> >> > the administrators managing the database servers using the SQL Server client
> >> > tools.
> >> >
> >> > Enterprise Manager
> >> > If the default database is not accessible you can not log on.
> >> >
> >> > Query Analyzer
> >> > If the default database is not accessible you can not log on.
> >> >
> >> > Management Studio
> >> > There is an option to specify a database in the connection that enables you
> >> > to log on, even if the default database is not accessible. However then the
> >> > graphical dialogs is not working.
> >> >
> >> > We're considering going away from using default databases (leaving it to
> >> > master on all logins).
> >> >
> >> > /Ola
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> This is, to the best of my knowledge, not deterministic. There is not setting for this. So, it
> >> >> could
> >> >> go either way. IMO, an app should never depend on default database - it should set the
> >> >> database
> >> >> in
> >> >> the connection string. You could, of course, add Login! as a login to SQL Server and specify
> >> >> the
> >> >> default database for that login (I'm pretty certain that this would be honored).
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> >> >> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
> >> >> > Hello!
> >> >> >
> >> >> > I have a question about default databases and Windows groups. A Windows
> >> >> > login is a member of two Windows groups. These two Windows groups have been
> >> >> > granted access to a sql server.
> >> >> >
> >> >> > Login1 Member of Group1 and Group2
> >> >> >
> >> >> > Group1 DefaultDbA
> >> >> > Group2 DefaultDbB
> >> >> >
> >> >> > How is the default database determined if Login1 is connecting to the sql
> >> >> > server?
> >> >> >
> >> >> > Best regards
> >> >> >
> >> >> > Ola Hallengren
> >> >>
> >> >>
> >>
> >>
>sql