I have several Logins on my server. One of them is Login '[BUILTIN\Administrators]' and an other of them is 'ORG\SqlAdministrators' which is based on a Windows NT Group. I'm the local OS admin on this machine and thus member of '[BUILTIN\Administrators]', but I'm also member of 'ORG\SqlAdministrators'. This Login has the sysadmin role on the SQL Server.
Do I have I chance to find out which of these two Logins are used, when I'm connection to SQL Server using SSMS? Which of these Logins make it possible for me to be able to connect? Is '[BUILTIN\Administrators]' always preferred over others?
(Background: These Logins have different default databases. If one of this databases is offline, I can't login any more. I will change all default dbs to 'master' to solve this. But I'm still curious which Login is used when I try to connect...)
asked Aug 03 '12 at 06:38 AM in Default
I do know that if you have an individual login and access via a group that you connect as the individual but gain any permissions from all logins your SID is associated with.
It may come down to your connection being the one whose SID is first found in your Windows ID ACL and has a match in SQL Server.
So it may just be that there is no guarantee if you have multiple Group memberships with SQL Server access. Good question.
When you say:
Does it matter which default is offline? If the Groups have different defaults it may be a way for you to dig deeper.
answered Aug 14 '12 at 08:47 PM
Personally I'd put your thoughts and efforts into removing the BUILTINAdministrators login entirely and control your security through correct windows authentication logins.....its the first thing I do on a SQL 2005 install.
answered Aug 14 '12 at 04:37 PM
not directly answering your question but picking up on something you mention regarding the default database. My preference is to set the default database (where there is no need for it to be a different one) to be TempDB.
The advantage here is that if anyone connects to the servers and forgets to connect to the correct database and then creates a number of objects they will be in TempDB, not Master. This means that when the server restarts the objects will be dropped automatically and not linger to cause problems or confusion in the future.
answered Aug 15 '12 at 10:33 AM