How to find out which Login is used?

Dear Folks,

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...)

more ▼

asked Aug 03, 2012 at 06:38 AM in Default

avatar image

2.2k 18 24 31

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

 SELECT * FROM sys.server_principals

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:

If one of this databases is offline, I can't login any more.

Does it matter which default is offline? If the Groups have different defaults it may be a way for you to dig deeper.

more ▼

answered Aug 14, 2012 at 08:47 PM

avatar image

12.1k 30 36 42

@Blackhawk-17: "Does it matter which default is offline?" Good question, in fact I can't login anymore if BUILTIN\\Administrators' default db is offline. I can still login if ORG\\SqlAdministrators' default db is offline... What does it mean?

Aug 15, 2012 at 12:13 PM eghetto

It sounds like my theory is correct and that the Administrators SID is presented and checked first from your Windows ID Access Token. As a result you are connecting under those credentials into SQL Server.

Aug 15, 2012 at 12:17 PM Blackhawk-17

@Blackhawk-17: Yeah, it sounds reasonable to me. I accept your answer until proven otherwise ;) Thanks!

Aug 15, 2012 at 12:22 PM eghetto

Try and set up a Profiler session and just monitor Audit Login. Makes sure to include the NTUserName, LoginName and LoginSID columns. Compare the SID with that in sys.server_principals. This may shed some light.

Aug 15, 2012 at 12:24 PM Blackhawk-17

I did so: NTUserName = Myname, LoginName = ORG\\Myname, LoginSID = 0X010500000000000515000000A2039B4... But I can't find this SID in sys.server_principals. How comes?

Aug 15, 2012 at 01:37 PM eghetto
(comments are locked)
10|1200 characters needed characters left

Personally I'd put your thoughts and efforts into removing the BUILTIN\Administrators login entirely and control your security through correct windows authentication logins.....its the first thing I do on a SQL 2005 install.

more ▼

answered Aug 14, 2012 at 04:37 PM

avatar image

60 1

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 15, 2012 at 10:33 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

I like your point of view, a reason more to set tempdb as default database.

Aug 15, 2012 at 12:07 PM eghetto
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 03, 2012 at 06:38 AM

Seen: 2215 times

Last Updated: Aug 15, 2012 at 02:04 PM

Copyright 2018 Redgate Software. Privacy Policy