March 2, 2017 at 12:18 am
Server authentication” vs “Windows authentication
Server Roles vs Database Roles
Logins vs Users
March 2, 2017 at 12:58 am
larsp777se - Thursday, March 2, 2017 12:18 AMServer authentication†vs “Windows authenticationServer Roles vs Database Roles
Logins vs Users
http://lmgtfy.com/?q=Server+authentication+vs+Windows+authentication
😎
March 2, 2017 at 1:38 am
Oh, the good old "Google is your friend"
I know this of course but you go to a forum hoping they can give a brief explanation and not the massive text you get when you google.
Now you have wasted my time by making me think you had a good answer and you wasted your own time providing it...
March 2, 2017 at 1:53 am
larsp777se - Thursday, March 2, 2017 12:18 AMServer authentication†vs “Windows authenticationServer Roles vs Database Roles
Logins vs Users
See, the way you've asked this question makes it look like homework that you haven't begun to think about for yourself. Hence Eirikur's response.
Some things to consider when pondering the differences...
Server authentication vs Windows authentication
SQL Server authentication - all login credentials dealt with by SQL Server; authentication also dealt with by SQL Server. Regarded as weaker than
Window authentication - security / login credentials passed over to Windows Active Directory. This means you can configure access via Windows AD groups rather than just individual users; on the downside, you have to have AD available in order to authenticate.
Server Roles vs Database Roles
Server role - permissions to do things that relate to the operation of the server (eg security administration, process admin)
Database role - roles at the individual database level - for example, you can configure one user to have full "database owner" rights in some databases, but not allow them any access at all to other databases.
Note that you can create custom roles to allow particular combinations of permissions - say, read/write on certain tables, no access to other tables, and can only execute these stored procedures/functions.
Logins vs Users
Logins are the server-level - access to the server is granted to a login (see SQL Server vs Windows above)
Users can be thought of as aliases for logins at the database level - access to individual database items is granted to the user (or role) within the database.
Thomas Rushton
blog: https://thelonedba.wordpress.com
March 2, 2017 at 2:01 am
Ok, I see.
No, there are no homework but I have come across these terms and a google suggests that the answer isn't as simple always as one might expect.
March 2, 2017 at 3:56 am
Thanks for the reply.
Could have narrowed down my questions I guess so I'm sorry about that.
So if I get this right:
Server authentication vs Windows authentication
Windows authentication is the higher level of authentication I guess. If you have that you doesn't need an sa-account which is disabled if you choose Windows authentication.
Server authentication means you need an sa-account which is often targeded.
You can have only Windows authentication but never only Server authentication (mixed mode)
You can create a group "doctors" with rights to see sensitive information in Windows authentication. Can't you do this with Server authentication?
Server Roles vs Database Roles
So if you have a Server Role you can be given different rights to administer the server itself. If you have a database role you can only change/look at databases. If you have Windows authentication and are admin, can you then do everything on the server and the databases?
Logins vs Users
Now here's the really tricky one I think.
So login can be sa (as Windows admin?) or with less rights to a server? Is then a login a server role?
A user can only access one (or more) databases?
March 2, 2017 at 8:07 am
This link might be helpful to understand more about permissions in SQL Server.
Server Roles and Database Roles are not exchangeable. They manage different permissions and should be properly used to prevent giving unnecessary permissions.
Logins will allow people to connect to the server. Users will allow people to connect to a database once connected to the server. This allows to have more granularity when handling permissions, especially when you have more than one user database on the same instance. Usually, a Login needs a User for it to be useful and vice versa.
March 2, 2017 at 8:21 am
larsp777se - Thursday, March 2, 2017 3:56 AMThanks for the reply.
Could have narrowed down my questions I guess so I'm sorry about that.So if I get this right:
Server authentication vs Windows authentication
Windows authentication is the higher level of authentication I guess. If you have that you doesn't need an sa-account which is disabled if you choose Windows authentication.Server authentication means you need an sa-account which is often targeded.
You can have only Windows authentication but never only Server authentication (mixed mode)
You can create a group "doctors" with rights to see sensitive information in Windows authentication. Can't you do this with Server authentication?
Server Roles vs Database RolesSo if you have a Server Role you can be given different rights to administer the server itself. If you have a database role you can only change/look at databases. If you have Windows authentication and are admin, can you then do everything on the server and the databases?
Logins vs UsersNow here's the really tricky one I think.
So login can be sa (as Windows admin?) or with less rights to a server? Is then a login a server role?
A user can only access one (or more) databases?
May I suggest that you take some time to read about these topics in Books Online (SQL Servers help file)? They are discussed in good detail and after reading those topics we can provide greater clarification.
March 2, 2017 at 8:40 am
Ok, I'll have a look in the online book.
March 2, 2017 at 8:40 am
larsp777se - Thursday, March 2, 2017 1:38 AMOh, the good old "Google is your friend"I know this of course but you go to a forum hoping they can give a brief explanation and not the massive text you get when you google.
Now you have wasted my time by making me think you had a good answer and you wasted your own time providing it...
But a Google search is not a waste of time. While I agree that searching through forum-answer after forum-answer can certainly be a waste of time, MS has all of this documented and learning to use Google to find the answers to such questions is incredibly important because a lot of the answers that you might get by asking on a forum can and frequently will be 1) less than adequate or 2) incorrect.
Using the lookup that Eirikur provided leads to a page where the second entry is the official MS documentation that not only identifies what they are and what the differences are, but some of the advantages and disadvantages of both are. You usually won't get such detailed information by asking a question on a forum. Once you've studied such a thing then, by all means, ask a question if the MSDN or TECHNET article caused some confusion for you.
Here's the link I'm talking about.
https://msdn.microsoft.com/en-us/library/ms144284.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2017 at 8:45 am
As a bit of a sidebar, it's a real shame that MS decided to offer "Books Online" as a separate product to be installed instead of making it a part of the installation process and be susceptible to CU updates and the like. A lot of people don't even know it exists today and, IMHO, it's usually a whole lot easier to find stuff in it rather than doing an online search especially because the online articles usually also have a bazillion links to follow even for some of the simple stuff. Of course, that can also cause you to miss some really cool stuff that people may have done in a given area.
"It Depends" but having the option to do either or both is the way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2017 at 8:57 pm
Jeff Moden - Thursday, March 2, 2017 8:40 AMlarsp777se - Thursday, March 2, 2017 1:38 AMOh, the good old "Google is your friend"I know this of course but you go to a forum hoping they can give a brief explanation and not the massive text you get when you google.
Now you have wasted my time by making me think you had a good answer and you wasted your own time providing it...
But a Google search is not a waste of time. While I agree that searching through forum-answer after forum-answer can certainly be a waste of time, MS has all of this documented and learning to use Google to find the answers to such questions is incredibly important because a lot of the answers that you might get by asking on a forum can and frequently will be 1) less than adequate or 2) incorrect.
Using the lookup that Eirikur provided leads to a page where the second entry is the official MS documentation that not only identifies what they are and what the differences are, but some of the advantages and disadvantages of both are. You usually won't get such detailed information by asking a question on a forum. Once you've studied such a thing then, by all means, ask a question if the MSDN or TECHNET article caused some confusion for you.
Here's the link I'm talking about.
https://msdn.microsoft.com/en-us/library/ms144284.aspx
I see your point.
My point is that most are well aware that you can google. You can asume that they have indeed googled before posting on a forum.
It only takes a second to google.It will probably take some time to get an answer on a forum. Maybe you forgott your password or have to register first.
After taking the time to do this you wait, hoping someone will give you some help. "Google is your friend" is not that help.. Also, the person posting that answer is probably well aware that I know that Google is my friend and is only making a (pointless) point. So they waste my time when I check this obvious answer.
All some "experts" in forums do is to enjoy pointing out the obvious it seems.
Sometimes they say. "You have to provide us with more detail or we can't help you"
I give the details and the person requesting this has nothing. They probably had no insight from the start but are only making a pointless point.
March 3, 2017 at 1:00 am
larsp777se - Thursday, March 2, 2017 8:57 PMYou can asume that they have indeed googled before posting on a forum.
No, you can't. In many, many cases people don't. The number of questions that could have been answered by a google search and reading the top hit is massive. The number that could have been answered by consulting the documentation are larger still.
Sometimes they say. "You have to provide us with more detail or we can't help you"
Which is entirely true in a lot of cases. Hell, I've had forum posts along the lines of "When I try to do <some trivial task>, I get an error. How do I fix it?" That cannot be answered in a useful way without more details. Or "My query's running slow, please give me the steps to resolve it.", with no query listed even
They probably had no insight from the start but are only making a pointless point.
Um, maybe look at people's posting history before you claim that they have no insight?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2017 at 1:12 am
A bit pedantic but the two authentication methods are Windows Authentication and SQL Server authentication.
If we omit the "SQL" then we have a confusing term in "Server Authentication" which can really mean just about anything including Windows Authentication.
This will be even more important to distinguish with SQL Server vNext on Linux where there is no Windows Authentication. If you say Server Authentication on a Linux box, would you be talking about your authentication to the Linux Server or to something else?
Good info on the differences between Windows Authentication and SQL Authentication have already been provided so I won't go into any more depth on that other than a key difference will be in where the source of the login is (windows or SQL Server) and where/how the password for each is stored/encrypted.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 3, 2017 at 1:57 am
GilaMonster - Friday, March 3, 2017 1:00 AMlarsp777se - Thursday, March 2, 2017 8:57 PMYou can asume that they have indeed googled before posting on a forum.No, you can't. In many, many cases people don't. The number of questions that could have been answered by a google search and reading the top hit is massive. The number that could have been answered by consulting the documentation are larger still.
Sometimes they say. "You have to provide us with more detail or we can't help you"
Which is entirely true in a lot of cases. Hell, I've had forum posts along the lines of "When I try to do <some trivial task>, I get an error. How do I fix it?" That cannot be answered in a useful way without more details. Or "My query's running slow, please give me the steps to resolve it.", with no query listed even
They probably had no insight from the start but are only making a pointless point.
Um, maybe look at people's posting history before you claim that they have no insight?
Well, ok. Maybe people are bad at googling but I still think it´s a useless answer. Even if people doesn´t google they still should know that you can. So I think it´s better to ignore the question if you aren´t giong to answer it.
As for more detail that is perfectly valid. But sometimes people seem write that without having a clue even with more detail, like another way of saying Google it. But that was a general remark and not aimed at this forum. And, yes, they can still be knowledgeable.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply