February 4, 2015 at 8:15 pm
Comments posted to this topic are about the item Login options
February 5, 2015 at 12:22 am
Thank you, Steve, for the post.
(I knew this by theory but never tried login creation for all options, except SQL auth login) 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 5, 2015 at 12:55 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 5, 2015 at 1:54 am
This was removed by the editor as SPAM
February 5, 2015 at 3:48 am
Time to complain about the question... (somebody always does)
A USER can be created for a certificate or asymmetric key, but they cannot Login:
Users that cannot authenticate These users cannot login to SQL Server.
* User without a login. Cannot login but can be granted permissions.
* User based on a certificate. Cannot login but can be granted permissions and can sign modules.
* User based on an asymmetric key. Cannot login but can be granted permissions and can sign modules.
(from https://msdn.microsoft.com/en-us/library/ms173463.aspx)
The question explicitly said "A login can be created", where the interpretation of the word login implies the ability to login into the database server. The documentation clearly says certificates and asymmetric keys cannot login.
February 5, 2015 at 5:25 am
Wow. I almost got this wrong because I so seldom use the other two login types. Fortunately I caught myself before hitting submit.
Nice question. It definately reminded me to check my assumptions at the door.
February 5, 2015 at 5:26 am
Stewart "Arturius" Campbell (2/5/2015)
Never created login for asymetric key before, so learned something new.
Me either. Good question.
February 5, 2015 at 5:29 am
matthew.flower (2/5/2015)
Time to complain about the question... (somebody always does)A USER can be created for a certificate or asymmetric key, but they cannot Login:
Users that cannot authenticate These users cannot login to SQL Server.
* User without a login. Cannot login but can be granted permissions.
* User based on a certificate. Cannot login but can be granted permissions and can sign modules.
* User based on an asymmetric key. Cannot login but can be granted permissions and can sign modules.
(from https://msdn.microsoft.com/en-us/library/ms173463.aspx)
The question explicitly said "A login can be created", where the interpretation of the word login implies the ability to login into the database server. The documentation clearly says certificates and asymmetric keys cannot login.
Well, if you want to be nitpicky...
SQL Server 2008 BOL
loginNameSpecifies the name of the login that is created. There are four types of logins: SQL Server logins, Windows logins, certificate-mapped logins, and asymmetric key-mapped logins. When creating logins mapped from a Windows domain account you must use the pre-Windows 2000 user logon name in the format [<domainName>\<loginName>]. You cannot use a UPN in the format loginName@DomainName. See example D later in this topic.
...
Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server. You can create a login from a certificate or asymmetric key only when the certificate or asymmetric key already exists in master.
It is Microsoft calling them logins. Plus the "cannot be used to connect to SQL Server" means a person cannot use them, because obviously the certificate store is using them, just as a background process to validate themselves.
February 5, 2015 at 5:37 am
I only raised it because I was expecting one of the sneaky gotcha type questions.
I know that User entries could be created for all of them, and immediately selected the last option, but second thoughts said that was too easy, so checked the documentation and found the differentiation between the two types of user - those that Login and those that can't.
February 5, 2015 at 5:39 am
matthew.flower (2/5/2015)
I only raised it because I was expecting one of the sneaky gotcha type questions.
Shame on Steve for making this simple! @=)
I know that User entries could be created for all of them, and immediately selected the last option, but second thoughts said that was too easy, so checked the documentation and found the differentiation between the two types of user - those that Login and those that can't.
Truth be told, I didn't actually know that information until you mentioned it. So it was a good point and taught me something. Thank you for that.
February 5, 2015 at 6:42 am
Ed Wagner (2/5/2015)
Stewart "Arturius" Campbell (2/5/2015)
Never created login for asymetric key before, so learned something new.Me either. Good question.
On the contrary 4 me, I knew this one as I have created this type of login b4 (business requirement) 😀
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
February 5, 2015 at 8:02 am
Hany Helmy (2/5/2015)
Ed Wagner (2/5/2015)
Stewart "Arturius" Campbell (2/5/2015)
Never created login for asymetric key before, so learned something new.Me either. Good question.
On the contrary 4 me, I knew this one as I have created this type of login b4 (business requirement) 😀
I had no idea, so I went with the most options possible. Based on this confession you may revoke my unearned point :hehe:
February 5, 2015 at 9:25 am
Steve Jones - SSC Editor (2/4/2015)
Comments posted to this topic are about the item <A HREF="/questions/Security/121685/">Login options</A>
Thanks for the question. I got it wrong but learned something.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 5, 2015 at 9:29 am
Nice Question
February 6, 2015 at 9:50 am
Interesting question, but extrremely easy for anyone who knows the syntax for CREATE LOGIN.
Why have two different wordings of the same wrong answer? (The third and fourth options both allow SQL auth, Windows users, and Windows groups.)
As mathew.flower pointed out the terminology is a bit confusing, but then people should have grown toexpect that from Microsoft. :hehe:
Tom
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply