October 31, 2013 at 9:44 am
Dscheypie (10/31/2013)
And I don't remember to have added the (select 4); this hint of course made it easy to solve this question without understanding the problem.I was baffled when a customer showed me this effect and let me look like a lemon, even more a grapefruit (bigger, and blushed a bit like I did), but very interesting I think...
Best regards
Jens-Peter
I imagine steve added it. It's something I didn't think about or I would have warned you that he would add it, as I believe it has become site policy to state how many boxes need to be ticked (because people used to claim that they thought only one box should be ticked when the statement wasn't included). A statement like "Tick as many options as are true" would be better, I think.
Tom
October 31, 2013 at 9:45 am
ronmoses (10/31/2013)
sknox (10/31/2013)
The following T-SQL code creates database users which map to the Windows groups. Every member of those groups, when logging into SQL Server, will have the rights of those database users.
USE DBall
CREATE USER TestSQLDBadmin FROM Login [Domain\TestWindowsDBadmins];
CREATE USER TestSQLDBreader FROM Login [Domain\TestWindowsDBreaders];
GO
Thank you. So if Disney gives Mickey Mouse the ability to fly, every member of The Mickey Mouse Club gains the same ability. Interesting, and entirely confusing. I think I'm not a dba for a reason.
ron
No, Ron, I think you are a good DBA, because this is not understandable on first look in my eyes! And if you feel confused I think you are not the only one.
To add to your metaphor: Mickey is member of the groups "Toons" and "Mice". Now Disney gave "Toons" the ability to fly (because there are also ducks in it) and "Mice" only the ability to run on earth (database). Now someone realized that not all member of "Toons" are supposed to fly (even Fantomias was before and after his heroic adventures an ordinary duck!) and removes the existance in the Disney universe for the group "Toons". In the Disney universe now toons do not exist (only the conceptual idea that Mickey is a toon, too), but on earth they still do, and can fly! Still I have the feeling this analogy needs some Adaptation 😀
And THAT is what I still do not understand totally!
Best regards
Jens-Peter
PS: By the way: I am leaving Asheville, NC; had a great PASS conference and return now to Germany; I will look for other posts later or tomorrow! Thanks for checking out this Question of the Day!
________________________________________________________
If you set out to do something, something else must be done first.
October 31, 2013 at 9:48 am
Ah, hi Tom!
Thanks for your valuable remarks and your work!
See you later...
Jens-Pater...
________________________________________________________
If you set out to do something, something else must be done first.
October 31, 2013 at 9:51 am
This was removed by the editor as SPAM
October 31, 2013 at 10:11 am
So if Disney gives Mickey Mouse the ability to fly, every member of The Mickey Mouse Club gains the same ability. Interesting, and entirely confusing.
Yes, but Mickey Mouse and Mickey Mouse Club exist in completely different worlds. Micky is not a member of his club.
In this example, Micky Mouse (database user) could be a character in video game (DB) and Mickey Mouse Club are actual people - it's the club of players of the game (SQL Login mapped to Windows Group). While they play the game, they control Mickey (server login -> db user mapping). So when Disney (DBA) gives Mickey Mouse ability to fly, it's the ability to fly inside the game, and every person playing the game gains that ability while they are in game.
Point of this question would be that when the Mickey Mouse Club is shut down, all players earlier belonging to the club can still play the game and fly, even if the club no longer exists..
...or at least something similar. It's not really a perfect analogy, but hopes it helps clear things at least a bit. 😀
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)
October 31, 2013 at 10:19 am
Thomas Abraham (10/31/2013)
BTW, I was surprised Steve didn't slip in a humorous Halloween question. I look forward to those holiday questions.
If you celebrate such things, Happy Halloween! Hope you get lots of >(///)<
Apologies. too much travel messed me up with planning here.
October 31, 2013 at 10:24 am
ronmoses (10/31/2013)
Thank you. So if Disney gives Mickey Mouse the ability to fly, every member of The Mickey Mouse Club gains the same ability. Interesting, and entirely confusing. I think I'm not a dba for a reason.ron
More like Disney gives the Mickey Mouse club (the group) the ability to fly and so Mickey Mouse who is a member of the club gets the ability to fly.
October 31, 2013 at 10:31 am
Dscheypie (10/31/2013)
No, Ron, I think you are a good DBA, because this is not understandable on first look in my eyes! And if you feel confused I think you are not the only one.
In essence it's because of disconnects here.
As a windows login, you present your credentials to servers when you log into them, including all of the groups and the Security IDentifiers of those groups.
SQL Stores Logins separate from database users.
The Login's created were for two groups and were associated with their SIDs in syslogins.
Then the Logins were used to create database users. Those users (which in this case are the SIDs of Groups, not individuals) have permissions granted to them.
When the login is deleted from syslogins, the user in the database is not deleted (this is how you can restore a database to another server and keep your permissions intact with a little clever system stored procedure work). The user in the database has the same SID as the Login in syslogins.
Your account authenticates with the regular user account, but belongs to both groups and has both SIDs within its token. When the database lets you in, it compares your collection of sids (both for your windows account and the groups that it belongs to) to it's internal sysusers list and finds that you belong to a user that has dbo permissions.
November 1, 2013 at 9:29 am
Good question!
I found some of these select N hints make these questions less valuable. "Select all the correct answers" is a better option.
Using n out m are the correct ones to figure out the answers is kind of "cheating" to me, and it defeats the purpose of learning the subject matter. No body testing your logical thinking capability.
November 1, 2013 at 12:19 pm
This was a real headscratcher. Thanks, Jens-Peter!
November 4, 2013 at 8:37 am
The various usages of "admin", "admins", "read", "reads", and "readers" tricked me up...
November 4, 2013 at 12:39 pm
Very nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 5, 2013 at 1:24 am
SqlMel (10/31/2013)
I find this one to be a very good question but one that can be easily answered by just analyzing the available options.First, you need to select 4 correct answers but there are three (The last ones) that are mutually exclusive. That means that the first three are true.
Given that, if you know for a fact that the user has permissions to the db_owner role, than you should know that he will have enough privileges to create a table in the database.
I'm surprised the percentage of correct answers are so low.
True.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
November 5, 2013 at 1:25 am
dhober (11/4/2013)
The various usages of "admin", "admins", "read", "reads", and "readers" tricked me up...
Almost tricked me up.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
November 5, 2013 at 1:35 am
jan.dewettinck (10/31/2013)
Nice question.Under drop the login with DBO rights you mention
DROP LOGIN [Domain\TestSQLDBadmins];
GO
I guess you meant
DROP LOGIN [Domain\TestWindowsDBadmins];
GO
?
Agree; TestSQLDBadmins is User not Login.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply