Checking database access

Question

Post reply

Checking database access

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
January 10, 2012 at 8:49 pm

#74695

Comments posted to this topic are about the item Checking database access
January 11, 2012 at 1:21 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Another nice easy question ......... Thanks

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

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Nice - keep em coming.

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

M&M SSC-Insane Points: 21700 More actions · Answer 3

M&M

SSC-Insane

Points: 21700

January 11, 2012 at 12:49 am

#1431600

Good question Steve.

M&M

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 4

AGGGH!

I missed the obvious, checked BOL, selected my option (singular) submitted.

Good question. Thanks

Ian

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 5

I like qotd about new and/or particular feature.

Thanks!

😀

paul s-306273 SSChampion Points: 10727 More actions · Answer 6

Nice question Steve.

Who's surprised that only 34% corrcet so far?

TomThomson SSC Guru Points: 104773 More actions · Answer 7

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.

Tom

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Is it Monday? Thanks for an easy one.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

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[/url]
Before posting a performance problem please read[/url]

mtassin SSC-Insane Points: 23099 More actions · Answer 10

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[/url]
For tips on how to post your problems[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 11

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 12

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.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 13

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,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA