February 15, 2022 at 11:12 am
Hello,
Whilst explaining about the default database assigned to a SQL login to a budding new DBA I encountered an unexpected situation: the database selected for a new query was not what my default database was configured to. I suspect that this is because my account is a member of multiple SQL logins owing to the logins being based on AD Groups. Can anyone confirm this and if so describe how the default database is selected when the user is a member of multiple logins?
Thanks
February 15, 2022 at 7:25 pm
When an account exists in multiple groups that have been added as logins to SQL Server - then the default database will effectively be randomly selected. That is because SQL will select the 'first' login (group) which may (or may not) be the same one on successive logins.
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
February 16, 2022 at 8:06 am
Random selection... that's what seems to be happening.
Do you know if Microsoft document that anywhere as I have been unable to find it?
February 16, 2022 at 4:52 pm
I am not aware of it being documented anywhere - but it makes sense that it would be random. It is all dependent on which group is selected when that account logs into the server. SQL has to pull the group membership for the login - then compare that group membership to the groups added to SQL.
And since permissions are cumulative - it needs to check every group and add those groups permissions if the login is a member of that group. One of those is selected as the first/default group - but which one is going to depend on the order of the groups returned for the login and the groups defined in SQL.
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
February 17, 2022 at 8:53 am
Thanks Jeffrey,
Based on what you're suggesting I might have thought that since the logon process has to work through all relevant groups then it would do so in a set order (alphabetical, date created, etc.) and should therefore be predictable as to which default database is selected. It's not a big issue for me, I was just trying to understand the process as I was embarrassed whilst training up a new team member that my expectations weren't borne out in reality.
Cheers,
Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply