April 22, 2010 at 10:40 am
Hey all,
At a bit of a loss here ... I'm part of an NT group that has sysadmin to this 2008 instance. When running a new query, it defaults me to a user database. I've checked all the logins, and there are three that default to this database, all NT groups ... none of which I'm a member of.
Any thoughts?
Thanks
April 23, 2010 at 12:32 am
This was removed by the editor as SPAM
April 23, 2010 at 6:35 am
stewartc-708166 (4/23/2010)
Check the following:- there is not a login for your user id, with default to that database
- the domain group you belong to might also be a member of another group, which does have that database as default.
Thanks Stewart, as stated, there are only 3 NT groups that have that database in question set as the default database, and I am not part of those groups either directly with my login, or through another group.
April 23, 2010 at 7:41 am
This was removed by the editor as SPAM
April 23, 2010 at 7:47 am
stewartc-708166 (4/23/2010)
in that case, next possibility is either application role or database role of which you might be a member.
Negative. The only group I'm part of on this instance is a sysadmin NT group, nothing more, nothing less.
April 23, 2010 at 8:11 am
Adam Bean (4/22/2010)
Any thoughts?
The only though I have is on this page it mentions:
When you first connect to an instance of SQL Server, the default database for the login is typically made the current database. However, you can specify a specific database as the current database at connect time. This request overrides the default database designated for the login.
So could it be the first time you connected to the instance it was to this user database?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 23, 2010 at 8:22 am
This was removed by the editor as SPAM
April 23, 2010 at 8:24 am
MeltonDBA (4/23/2010)
Adam Bean (4/22/2010)
Any thoughts?The only though I have is on this page it mentions:
When you first connect to an instance of SQL Server, the default database for the login is typically made the current database. However, you can specify a specific database as the current database at connect time. This request overrides the default database designated for the login.
So could it be the first time you connected to the instance it was to this user database?
A good thought, but no. This database was created well after the instance was brought up. Yeah, I could over ride the default setting in SSMS, but I'd still like to understand how/why this is happening.
April 23, 2010 at 8:26 am
stewartc-708166 (4/23/2010)
that has effectively stymied me.I have checked the creation of a user, without specifying a default database, and it automatically defaults to "master".
You and me both. I just dropped and re-created the NT group with sysadmin, default database master, and new query still defaults me to this user database. Crazy ...
April 23, 2010 at 8:55 am
Adam Bean (4/23/2010)
You and me both. I just dropped and re-created the NT group with sysadmin, default database master, and new query still defaults me to this user database. Crazy ...
Could you post the query?
Does it do it with any query you run or just this particular one?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 23, 2010 at 9:00 am
IF EXISTS (SELECT [name] FROM sys.server_principals WHERE [name] = 'MyDomain\MyGroup')
DROP LOGIN [MyDomain\MyGroup]
GO
CREATE LOGIN [MyDomain\MyGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
When running a new query in SSMS, it defaults me to a user database. When connecting as sa or any other account I create (with or without sa rights), it defaults me to master as it should.
So, let's look at what has that database set as the default:
SELECT [name] FROM sys.server_principals
where [default_database_name] = 'DBInQuestion'
Results in three NT groups. I've checked AD, and I am not part of any of those NT groups, either explicitly or through a nested group.
Only other area that comes to mind is that when connecting to an instance via SSMS, to check out the options. Under connection properties, connect to database is set to <default>.
I don't get it. This is the only instance of over 300 that I have this issue on.
April 23, 2010 at 9:04 am
Just a shot in the dark...but on the Connect to Server prompt when you open SSMS look at Connection Properties and see if "Connect to database" is set to <default> or the user database in question.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 23, 2010 at 9:23 am
MeltonDBA (4/23/2010)
Just a shot in the dark...but on the Connect to Server prompt when you open SSMS look at Connection Properties and see if "Connect to database" is set to <default> or the user database in question.
See above, it's set to <default> ... I'm at a loss. I want to try changing the three NT groups default db to master, but it may break their application. Not sure what else to look into as this simply doesn't make sense.
April 23, 2010 at 12:46 pm
How are you connecting to the database? If you are using a registered server - check to see what the default setting is for that registered server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2010 at 1:34 pm
Jeffrey Williams-493691 (4/23/2010)
How are you connecting to the database? If you are using a registered server - check to see what the default setting is for that registered server.
SSMS/SQLCMD, doesn't matter, always defaults me regardless to as if it's registered or not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply