March 5, 2017 at 10:52 am
Heh... the reason why Google "doesn't" help here is because you've not Googled enough. 😉 Security in Windows and SQL Server is a huge subject that entire books have been written about. There is no real simple explanation for any of it. You either know it all (about what you’re trying to do) or you don't. In other words, you usually won’t find a “cook book” with the exact recipe you want in it and, if you do, you need to know a whole lot of lingo like what a teaspoon is and maybe what they mean by a “pinch of salt” and what the difference is between a small egg and a large egg is. 😉
Someone like Gail Shaw will likely make corrections to this but this is my over-simplified explanation of it at a 60,000 foot level.
First, I'm speaking only of Windows Authentication...
... and some over-simplified definitions .so that you know what a “teaspoon” is..
AD
A common abbreviation for Windows "Active Directory"
User
This is actually a bit of a confusing term because it has two meanings depending on what you're referring to:
1. An individual that has been granted access privs to the windows domain through Active Directory. To avoid the confusion, I'll refer to this definition of user as "AD User".
2. An entity created in a database using “CREATE USER”. It may or may not be associated with a “Login” although, in order to avoid mapping confusion”, most people will create user names that are the same as Login names. It is possible to create a user name than doesn’t match or even relate to a login although I’ve not found cause to ever do so. To avoid confusion, I’ll use the term “DB User” for this type of user.
Group
A group of AS Users that have been assigned membership of an Active Directory "group". To further confuse, once a group is added to the security of SQL Server, it will mostly be treated as a "user" when it comes to databases (can't have its own schema, for example).
Principle
A general term for logins, groups, users, and some other things, just to further confuse.
Logins
An AD user, AD group, or SQL Server Authentication principal whose name has been added to the SQL Server instance (just "server" from here on) through the "Security" folder of the "Object Explorer" window in SSMS (can also be done using T-SQL but not going there). Generally speaking, this means that the AD user or AD group (principle) can connect to the server but won't have much else in the area of privs. (You can also deny the ability to connect or disable the login).
Like I said, this can also be a login that uses SQL Server Authentication but we're not going there. (although a lot of the same rules apply).
Roles
There are two sets of roles and I'm speaking only of the roles available by default in SQL Server (you CAN build your own but not going there).
1. Server-wide roles (I'll just call them "Server Roles from here on") that gives a "Principle" privs on all databases on the server.
2. Database roles, which give a Principle (DB User, in this case) privs only on a given database. Each database has its own set of roles available.
Note that you do not “give” a role to a DB user or login. You make the DB user or login (principle) a member of the DB or System role. In plain English, you don’t assign the role to the principle. Rather you make the principle a member of the role and the principle inherits the privs of the role(s) that the principle is a member of. The difference is subtle but important.
And, now, to try to answer your questions.
“If I am added to the role doctor, I can login as doctor with all rights the doctor-role have?”
If the person(s) that setup you AD user login have done things correctly then, no. You cannot login as “Doctor”. You can login only as you.
Once Windows has authenticated you as an AD User, it’ll check for logins on SQL Server when you try to access it. If your AD User has been added as a login or your AD user has been added to an AD group and that AD group has been added as a login on SQL Server, then you’re allowed to connect to the SQL Server.
If you try to access a database, then SQL server checks the server wide roles a grants you certain privs that your AD User or the AD Group you belong to has been assigned, which may be nothing when it comes to databases.
Once it’s done checking the server level roles, then it checks the DB users that have been assigned to the database that you’re trying to access. If your AD login or the AD group that you belong to has been created as a DB User, then it checks for DB Roles that the individual DB user has been assigned and it also checks for privs on individual objects that may have been assigned to the DB user.
If that DB user is member of the “doctor” role, then you will have the privs assigned to that role less any that may have been specifically denied for that DB user (which is normally either mapped to your or the group you belong to).
“If I am added to the group doctors, I can do the same. Login as doctor with all is right.”
For the same semantic reasons as above, the actual answer is no. You cannot login as “doctor”. You can only login as you. Everything concerning the preceding question above applies.
“The differens is that I cant´t give the role of a doctor to another user even if I have that role.”
If the Windows Admin and DBA have both done their jobs correctly then, yes, that will be true. You cannot add others to the role of “doctor” in any given database that has the role. If the DBA screwed up, then either your AD Login or the AD Group that you belong to may have the privs to add people to a given role. Again, I usually consider that to be a major screw up unless an individual DB user has been assigned the “dbo” priv (which could also be a mistake).
“If I belong to a group of doctors I can invite someone to our group, and that person have the same rights as the rest of the group?”
Unless the DBA made the mistake I spoke of in the preceding question above, then no… you can’t make someone else a member of the “doctors” group even though you’re a member of that group.
And all of THAT is just the simple explanation. There are nuances, caveats, and details that relate to your “simple” questions that I’d have to write a book for. There are password policies to follow both in Windows and SQL Server. You need to read about the principle of “minimal/least privs”. You need to learn about how to override privs assigned to groups by individual user. You need to learn about inheritance of privs. And the list goes on and on.
Eirikur was correct and, again, I don’t mean any of this as a slam. “Google is your friend” here but not the way you think. Security in SQL Server is a huge subject and, based on your original question, Eirikur should have made the realization that you don’t actually know anything about it and should have given you a better Google search. He should also have made you aware that if you go for the simple answers here, you will have simple “security” that can be easily penetrated and you’ll end up reading about how you did it wrong in the morning news someday.
Now, it’s up to you. Use “security in sql server msdn.microsoft” as your Google search and start studying. As you’re studying, make sure you write down the questions similar to those that you’ve asked here and, when you find the answer, write it down after the question. Then, practice, practice, practice. Setup different scenarios on a test box and experiment until you get it right. Then, tear it down and do it again until you don’t need to even think to get it right.
Yeah… it’s that important and until you understand the documentation that Microsoft wrote, you have no chance of blogs helping you because you don’t actually know what a “teaspoon” is yet. 😉 Rather than just trying to teach you to fish, I'm trying to tell you that it's not as simple as your "request for a recipe" questions and that you need to continue to Google for things so that you don't end up with a rats nest in the reel. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2017 at 11:42 am
larsp777se - Sunday, March 5, 2017 7:02 AMIf I am added to the role doctor, I can login as doctor with all rights the doctor-role have?
No.
You would log in as you, not as a 'doctor'. If you can log in, then you get the permissions associated with the role 'doctor'. Being a member of a role doesn't mean you can log in. I can deny you connect, I can disable your login.
Roles are about authorisation.
If I am added to the group doctors, I can do the same. Login as doctor with all is right.
If the domain group can log in, and you are a member of the domain group, you can log in. You get whatever permissions are granted to the group, directly or via roles.
Groups are about authentication.
The differens is that I cant´t give the role of a doctor to another user even if I have that role. If I belong to a group of doctors I can invite someone to our group, and that person have the same rights as the rest of the group?
Neither of those is true.
To grant a role you need specific permissions in SQL. To add a domain login to a domain group you need specific permissions on the domain.
And I agree with Jeff, you should probably go and read the documentation from MS, and as much other stuff that google turns up as you can, because this is a huge subject. You're welcome to ask specific questions, but we can't rewrite the docs.
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 5, 2017 at 11:47 am
larsp777se - Sunday, March 5, 2017 6:45 AMHowever, you still need to be added as a user to a database to be able to access it? Does that mean that that use the same username and password as with Windows or doesn't need one at all?
Users map logins to databases, users don't have passwords because you don't log in as a user. You log in with either Windows Authentication or SQL authentication. That login is mapped to 0 or more databases, in any database it's mapped to you have a database user.
Server-level permissions are granted to the login. Database-level permissions are granted to database users.
I recommend https://blogs.msdn.microsoft.com/lcris/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals/ (which I found via google search for "SQL Server logins and users"). It's old, but still valid.
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 6, 2017 at 12:16 am
Heh... the reason why Google "doesn't" help here is because you've not Googled enough. 😉
Well, I guess Google is your friend in many cases... 🙂
But this was no attempt to continue the LMGTFY debate so I'm sorry about that.
I really want to understand but as you pointed out that is not an easy task.
But I appreciate your attempt and will read your answers a bit later.
March 6, 2017 at 1:53 am
Jeff Moden - Sunday, March 5, 2017 10:52 AMHeh... the reason why Google "doesn't" help here is because you've not Googled enough. 😉 Security in Windows and SQL Server is a huge subject that entire books have been written about. There is no real simple explanation for any of it. You either know it all (about what you’re trying to do) or you don't. In other words, you usually won’t find a “cook book†with the exact recipe you want in it and, if you do, you need to know a whole lot of lingo like what a teaspoon is and maybe what they mean by a “pinch of salt†and what the difference is between a small egg and a large egg is. 😉
Someone like Gail Shaw will likely make corrections to this but this is my over-simplified explanation of it at a 60,000 foot level.
First, I'm speaking only of Windows Authentication...
... and some over-simplified definitions .so that you know what a “teaspoon†is..
AD
A common abbreviation for Windows "Active Directory"User
This is actually a bit of a confusing term because it has two meanings depending on what you're referring to:
1. An individual that has been granted access privs to the windows domain through Active Directory. To avoid the confusion, I'll refer to this definition of user as "AD User".
2. An entity created in a database using “CREATE USERâ€. It may or may not be associated with a “Login†although, in order to avoid mapping confusionâ€, most people will create user names that are the same as Login names. It is possible to create a user name than doesn’t match or even relate to a login although I’ve not found cause to ever do so. To avoid confusion, I’ll use the term “DB User†for this type of user.Group
A group of AS Users that have been assigned membership of an Active Directory "group". To further confuse, once a group is added to the security of SQL Server, it will mostly be treated as a "user" when it comes to databases (can't have its own schema, for example).Principle
A general term for logins, groups, users, and some other things, just to further confuse.Logins
An AD user, AD group, or SQL Server Authentication principal whose name has been added to the SQL Server instance (just "server" from here on) through the "Security" folder of the "Object Explorer" window in SSMS (can also be done using T-SQL but not going there). Generally speaking, this means that the AD user or AD group (principle) can connect to the server but won't have much else in the area of privs. (You can also deny the ability to connect or disable the login).Like I said, this can also be a login that uses SQL Server Authentication but we're not going there. (although a lot of the same rules apply).
Roles
There are two sets of roles and I'm speaking only of the roles available by default in SQL Server (you CAN build your own but not going there).
1. Server-wide roles (I'll just call them "Server Roles from here on") that gives a "Principle" privs on all databases on the server.
2. Database roles, which give a Principle (DB User, in this case) privs only on a given database. Each database has its own set of roles available.Note that you do not “give†a role to a DB user or login. You make the DB user or login (principle) a member of the DB or System role. In plain English, you don’t assign the role to the principle. Rather you make the principle a member of the role and the principle inherits the privs of the role(s) that the principle is a member of. The difference is subtle but important.
And, now, to try to answer your questions.
“If I am added to the role doctor, I can login as doctor with all rights the doctor-role have?â€
If the person(s) that setup you AD user login have done things correctly then, no. You cannot login as “Doctorâ€. You can login only as you.
Once Windows has authenticated you as an AD User, it’ll check for logins on SQL Server when you try to access it. If your AD User has been added as a login or your AD user has been added to an AD group and that AD group has been added as a login on SQL Server, then you’re allowed to connect to the SQL Server.
If you try to access a database, then SQL server checks the server wide roles a grants you certain privs that your AD User or the AD Group you belong to has been assigned, which may be nothing when it comes to databases.
Once it’s done checking the server level roles, then it checks the DB users that have been assigned to the database that you’re trying to access. If your AD login or the AD group that you belong to has been created as a DB User, then it checks for DB Roles that the individual DB user has been assigned and it also checks for privs on individual objects that may have been assigned to the DB user.
If that DB user is member of the “doctor†role, then you will have the privs assigned to that role less any that may have been specifically denied for that DB user (which is normally either mapped to your or the group you belong to).
“If I am added to the group doctors, I can do the same. Login as doctor with all is right.â€
For the same semantic reasons as above, the actual answer is no. You cannot login as “doctorâ€. You can only login as you. Everything concerning the preceding question above applies.
“The differens is that I cant´t give the role of a doctor to another user even if I have that role.â€
If the Windows Admin and DBA have both done their jobs correctly then, yes, that will be true. You cannot add others to the role of “doctor†in any given database that has the role. If the DBA screwed up, then either your AD Login or the AD Group that you belong to may have the privs to add people to a given role. Again, I usually consider that to be a major screw up unless an individual DB user has been assigned the “dbo†priv (which could also be a mistake).
“If I belong to a group of doctors I can invite someone to our group, and that person have the same rights as the rest of the group?â€
Unless the DBA made the mistake I spoke of in the preceding question above, then no… you can’t make someone else a member of the “doctors†group even though you’re a member of that group.
And all of THAT is just the simple explanation. There are nuances, caveats, and details that relate to your “simple†questions that I’d have to write a book for. There are password policies to follow both in Windows and SQL Server. You need to read about the principle of “minimal/least privsâ€. You need to learn about how to override privs assigned to groups by individual user. You need to learn about inheritance of privs. And the list goes on and on.
Eirikur was correct and, again, I don’t mean any of this as a slam. “Google is your friend†here but not the way you think. Security in SQL Server is a huge subject and, based on your original question, Eirikur should have made the realization that you don’t actually know anything about it and should have given you a better Google search. He should also have made you aware that if you go for the simple answers here, you will have simple “security†that can be easily penetrated and you’ll end up reading about how you did it wrong in the morning news someday.
Now, it’s up to you. Use “security in sql server msdn.microsoft†as your Google search and start studying. As you’re studying, make sure you write down the questions similar to those that you’ve asked here and, when you find the answer, write it down after the question. Then, practice, practice, practice. Setup different scenarios on a test box and experiment until you get it right. Then, tear it down and do it again until you don’t need to even think to get it right.
Yeah… it’s that important and until you understand the documentation that Microsoft wrote, you have no chance of blogs helping you because you don’t actually know what a “teaspoon†is yet. 😉 Rather than just trying to teach you to fish, I'm trying to tell you that it's not as simple as your "request for a recipe" questions and that you need to continue to Google for things so that you don't end up with a rats nest in the reel. 😉
I think you mean database principals rather than principles here Jeff. A search for one produces very different results from a search for the other.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 6, 2017 at 4:54 am
Wow, sorry I asked 🙂
Thanks for all the clarification to all.
A complicated issue indeed.
One last "probably stupid question" (after all your explanation)
Can you say that a login can be assigned to a Server role and a user to a database role?
March 6, 2017 at 5:47 am
larsp777se - Monday, March 6, 2017 4:54 AMCan you say that a login can be assigned to a Server role and a user to a database role?
Server roles can only be granted server-level permissions and database roles can only be granted database-level permissions (you can't GRANT SELECT on a table to a server role and you can't grant ALTER ANY LOGIN to a database-level role)
and
GilaMonster - Sunday, March 5, 2017 11:47 AMServer-level permissions are granted to the login. Database-level permissions are granted to database users.
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 6, 2017 at 5:55 am
GilaMonster - Monday, March 6, 2017 5:47 AMGilaMonster - Sunday, March 5, 2017 11:47 AMServer-level permissions are granted to the login. Database-level permissions are granted to database users.Sorry, missed that. Said it was a stupid question 😉
March 6, 2017 at 7:59 am
Neil Burton - Monday, March 6, 2017 1:53 AMI think you mean database principals rather than principles here Jeff. A search for one produces very different results from a search for the other.
It's a rather lengthy reply that I made. To which section are you referring to?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2017 at 8:08 am
Jeff Moden - Monday, March 6, 2017 7:59 AMNeil Burton - Monday, March 6, 2017 1:53 AMI think you mean database principals rather than principles here Jeff. A search for one produces very different results from a search for the other.It's a rather lengthy reply that I made. To which section are you referring to?
Most of the way through it you refer to principles rather than principals.For example
Principle
A general term for logins, groups, users, and some other things, just to further confuse.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 6, 2017 at 9:45 pm
Ah...yup. Bad spelling on my part. Thanks for pointing it out. Hopefully, the OP understood the principle I was trying to get across. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2017 at 10:26 pm
Windows authentication must be used when working in an Intranet type of an environment.
SQL Server authentication can be used in all the other type of cases.
March 7, 2017 at 12:31 am
johnwalker10 - Monday, March 6, 2017 10:26 PMWindows authentication must be used when working in an Intranet type of an environment.SQL Server authentication can be used in all the other type of cases.
Not true.
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
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply