I No Longer Have Access to My DB and I'm a DBCreator!

Question

I No Longer Have Access to My DB and I'm a DBCreator!

Viewing 8 posts - 16 through 22 (of 22 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Interesting question - but the title does seem to contain the answer.

To run that on my system I would have to switch security to allow SQL users as well as windows users, and then switch it back again when finished. But first I would have to either log in on the PC as a privileged user or run SQLCMD as a privileged user, instead of being my normal non-privileged user. Too much hassle, and against my self-imposed rules for a non-emergency situation, so I didn't make any attempt to run it despite the question actually telling us to run it - the second one which did that recently. The SQLCMD script language isn't hard to understand, so it seemed easy to see what would happen even without the title.

Hugo's experience seems odd - I can't see any way that the user has access to the database after the change of owner. Since I don't have sql users onmy system, I can't test to see if I get the same result.

Tom

Roddy.CAMERON SSCommitted Points: 1550 More actions · Answer 2

Hi Hugo

The only reason I can think of is that your "model" database has the "guest" user enabled with read permissions so your login is getting access to the new database as guest, rather than as itself??

Cheers

Roddy

Revenant SSC-Forever Points: 42467 More actions · Answer 3

L' Eomot Inversé (1/23/2014)
Interesting question - but the title does seem to contain the answer.
To run that on my system I would have to switch security to allow SQL users as well as windows users, and then switch it back again when finished. But first I would have to either log in on the PC as a privileged user or run SQLCMD as a privileged user, instead of being my normal non-privileged user. Too much hassle, and against my self-imposed rules for a non-emergency situation, so I didn't make any attempt to run it despite the question actually telling us to run it - the second one which did that recently. The SQLCMD script language isn't hard to understand, so it seemed easy to see what would happen even without the title.
Hugo's experience seems odd - I can't see any way that the user has access to the database after the change of owner. Since I don't have sql users on my system, I can't test to see if I get the same result.

SQL Server security is getting quite complex, doesn't it? And is may vary with the fixes that you did or did not install. Well, that's what keeps us in ranks of reasonably well paid professionals.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sneumersky SSCertifiable Points: 7667 More actions · Answer 5

.....and this is why I am NOT a DBA by trade....

0 points for me.

Hany Helmy SSChampion Points: 13921 More actions · Answer 6

Toreador (1/23/2014)
Interesting question, but the title meant it was easy to get the right answer.
Though it looks like almost half the people who answered didn't read the title 😉

+1 True, the title made it easy to answer; yet I found it a good & interesting question.

Thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 7

PChiragS

SSCarpal Tunnel

Points: 4965

January 28, 2014 at 9:39 am

#1684003

interesting question..

Thanks Andy for great QOTD.

sqlnaive SSCoach Points: 17437 More actions · Answer 8

It was a very good question. The first two were simple but the third one was tricky. 🙂