Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Checking database access Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 5,469, Visits: 23,457
Still a suprisingly low correct

Correct answers: 35% (100)
Incorrect answers: 65% (183)
Total attempts: 283



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1233920
Posted Wednesday, January 11, 2012 6:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 3,566, Visits: 72,411
L' Eomot Inversé (1/11/2012)
Nice and simple.

I'm very surprised there's only 34% correct so far, although I would expect the wiggle on whether no other user is connected to a db which is in single-user mode would catch a few who didn't think hard (it returns 1 if an attempt to connect would have succeeded, which makes it pretty obvous that it can't return 1 if someone else is connected in single user mode, but while Vulcans might always get something like that right us poor humans sometimes don't think hard enough), but that doesn't seem to account for enough of the wrong answers.


I got it wrong, because instead of BOL, I just used SSMS's SQL Help (2008 R2). For that I have


Remarks
HAS_DBACCESS returns 1 if the user has access to the database, 0 if the user has no access to the database, and NULL if the database name is not valid.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1233943
Posted Wednesday, January 11, 2012 6:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 11,168, Visits: 10,927
L' Eomot Inversé (1/11/2012)
Nice and simple.

I see a lot of people saying this, but either my product knowledge is significantly below average or we're doing something differently. I don't, for example, check BOL before answering - I do it on the spot. That's not to say either way is right or wrong, just a potential point of difference.

I'm very surprised there's only 34% correct so far, although I would expect the wiggle on whether no other user is connected to a db which is in single-user mode would catch a few who didn't think hard (it returns 1 if an attempt to connect would have succeeded, which makes it pretty obvous that it can't return 1 if someone else is connected in single user mode, but while Vulcans might always get something like that right us poor humans sometimes don't think hard enough), but that doesn't seem to account for enough of the wrong answers.

This function is one I don't use much. I know what it does, but I hovered between single-user and suspect as the second option to the obvious first correct answer. I have no experience of executing HAS_DBACCESS against a single-user mode database or a suspect one, so I took a guess.

edit: I should add that I am unsure of the value of this question, beyond one that simply asked a 'nice and simple' question about what HAS_DBACCESS does in normal circumstances. I don't expect to remember the second correct option for any length or time, or that it would ever be important for me to do so. Overall: meh.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1233945
Posted Wednesday, January 11, 2012 7:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 04, 2013 12:06 PM
Points: 989, Visits: 1,804
SQL Kiwi (1/11/2012)
L' Eomot Inversé (1/11/2012)
Nice and simple.

I see a lot of people saying this, but either my product knowledge is significantly below average or we're doing something differently. I don't, for example, check BOL before answering - I do it on the spot. That's not to say either way is right or wrong, just a potential point of difference.

I'm very surprised there's only 34% correct so far, although I would expect the wiggle on whether no other user is connected to a db which is in single-user mode would catch a few who didn't think hard (it returns 1 if an attempt to connect would have succeeded, which makes it pretty obvous that it can't return 1 if someone else is connected in single user mode, but while Vulcans might always get something like that right us poor humans sometimes don't think hard enough), but that doesn't seem to account for enough of the wrong answers.

This function is one I don't use much. I know what it does, but I hovered between single-user and suspect as the second option to the obvious first correct answer. I have no experience of executing HAS_DBACCESS against a single-user mode database or a suspect one, so I took a guess.

edit: I should add that I am unsure of the value of this question, beyond one that simply asked a 'nice and simple' question about what HAS_DBACCESS does in normal circumstances. I don't expect to remember the second correct option for any length or time, or that it would ever be important for me to do so. Overall: meh.


I agree on the "not so obvious". I also didn't use BOL but reasoned it like this -- you can't USE a DB when it is suspect and most of the time you have to USE a DB to run a function against it, so since you can't use a suspect DB you can't run this function against it. Obviously based upon the answer you must be able to run it against any DB regardless of connection context.
Post #1233977
Posted Wednesday, January 11, 2012 8:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:36 AM
Points: 896, Visits: 1,479
SQL Kiwi (1/11/2012)
This function is one I don't use much. I know what it does, but I hovered between single-user and suspect as the second option to the obvious first correct answer. I have no experience of executing HAS_DBACCESS against a single-user mode database or a suspect one, so I took a guess.


That's exactly what was on my mind and exactly the same answers I marked.

Best regards,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1234018
Posted Wednesday, January 11, 2012 8:52 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 4,231, Visits: 3,315
Got this one wrong, but learned something...

Thanks, Steve!
Post #1234060
Posted Wednesday, January 11, 2012 9:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
tks for the question
Post #1234094
Posted Wednesday, January 11, 2012 10:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:28 AM
Points: 2,381, Visits: 1,007
ARRGH! should read the WHOLE question and choose two!
Post #1234189
Posted Wednesday, January 11, 2012 10:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 5,845, Visits: 12,572
good question I think. Reminds people of the function (or brings it to their attention for the first time) so its educational, but steve put a little twist in it so you had to think and not just google it.

---------------------------------------------------------------------

Post #1234201
Posted Thursday, January 12, 2012 5:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 1,253, Visits: 13,546
Good question!!!

Thanks Steve!!!!!



rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1234677
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse