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

Question

Post reply

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

Andy Warren

SSC Guru

Points: 119902
More actions
January 22, 2014 at 11:24 pm

#151314

Comments posted to this topic are about the item I No Longer Have Access to My DB and I'm a DBCreator!
Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
January 23, 2014 at 3:02 am

This was removed by the editor as SPAM
January 23, 2014 at 7:35 am

This was removed by the editor as SPAM

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

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

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Interesting QotD thanks.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Carlo Romagnano SSC-Insane Points: 22663 More actions · Answer 2

Andy Warren (1/22/2014)
Comments posted to this topic are about the item <A HREF="/questions/Security/105935/">I No Longer Have Access to My DB and I'm a DBCreator!</A>

Easy one! Thanks!

😀

Toreador SSChampion Points: 11365 More actions · Answer 3

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 😉

Todd Reddinger Ten Centuries Points: 1362 More actions · Answer 4

I'm one of the ones who did not read the title. I hate missing a question like that!

Live and learn... and learn .... and learn

Todd

Thanks,

ToddR

george sibbald SSC Guru Points: 104210 More actions · Answer 5

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 😉

count me in that half! Obvious if you do.

Also, the question should specify this is SQL2012 specific (alter server role).

you would also need to be logged onto the server when running SSMS to use localhost

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

Mighty SSCrazy Eights Points: 9063 More actions · Answer 6

Also, the question should specify this is SQL2012 specific (alter server role).

I wonder how many people gave an "incorrect" answer, based on the fact that they were not using SQL2012, but an older version. Then the correct options would have 1, 4 and 6.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 7

Got it wrong, but also got this when I ran the script in SQLCMD mode:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ROLE'.

Running on 2008. Is it just a 2008/2012 issue? (If so, should have been posted with question.) Or am I missing something else?

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

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Stewart "Arturius" Campbell (1/23/2014)
ALTER SERVER ROLE is new functionality added with SQL2012

Thanks Stewart for the info.

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

cjohnson 44616 Grasshopper Points: 10 More actions · Answer 9

This is the first time I have tried answering one of these questions. I did notice that the title seemed to give away the answer, so I thought that might be a trick. Even so it seemed like a fairly simple example, so despite the fact that I am a total newbie, I thought I might be able to get it right. HOWEVER, I didn't notice the "(Choose 3)" in the instructions and thought I had to choose my (one) "answer" so tried to select the answer that was the "most" correct, i.e., the one that reflected the final result, not all of the other results that would occur in between. Sigh. OK, note to self: Read all the instructions carefully before starting the exercise.

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 10

Hummm... If I knew this was 2012 specific then I would have probably not attempt to run it. Here is my result when running it in SS2K8R2

--run this in sqlcmd mode

USE [master]

Changed database context to 'master'.

CREATE LOGIN [QODMaster] WITH PASSWORD=N'takethepebblefrommyhand', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHEC

K_POLICY=OFF

ALTER SERVER ROLE [dbcreator] ADD MEMBER [QODMaster]

Msg 102, Level 15, State 1, Server DNY02F03W078, Line 1

Incorrect syntax near 'ROLE'.

Sqlcmd: Successfully connected to server 'localhost'.

create database QODTest

Msg 262, Level 14, State 1, Server DNY02F03W078, Line 2

CREATE DATABASE permission denied in database 'master'.

use QODTest

Msg 911, Level 16, State 1, Server DNY02F03W078, Line 1

Database 'QODTest' does not exist. Make sure that the name is entered correctly.

select * into MyDatabases from sys.databases

Msg 262, Level 14, State 1, Server DNY02F03W078, Line 1

CREATE TABLE permission denied in database 'master'.

Sqlcmd: Successfully connected to server 'localhost'.

alter authorization on database::QODTest to sa

Msg 15151, Level 16, State 1, Server DNY02F03W078, Line 2

Cannot find the database 'QODTest', because it does not exist or you do not have permission.

Sqlcmd: Successfully connected to server 'localhost'.

use QODTest

Msg 911, Level 16, State 1, Server DNY02F03W078, Line 2

Database 'QODTest' does not exist. Make sure that the name is entered correctly.

select * from MyDatabases

Msg 208, Level 16, State 1, Server DNY02F03W078, Line 1

Invalid object name 'MyDatabases'.

Sqlcmd: Successfully connected to server 'localhost'.

drop database QODTest

drop login QODMaster

Msg 3701, Level 11, State 1, Server DNY02F03W078, Line 2

Cannot drop the database 'QODTest', because it does not exist or you do not have permission.

C:\Users\k1zimmer\Desktop>

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

Can anyone explain to me why the script runs without error on my system, every time I try it?

The only things I changed are:

* Change "localost" to "perFact\SQL2012" in the :connect commands (I'm running on a named instance)

* Add a USE tempdb before the DROP DATABASE (otherwise it would hang, waiting for the DB to be no longer in use)

Here's a copy/paste of my code. I am sure I miss someting elementary - but what?

--run this in sqlcmd mode

USE [master]

GO

CREATE LOGIN [QODMaster] WITH PASSWORD=N'takethepebblefrommyhand', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER SERVER ROLE [dbcreator] ADD MEMBER [QODMaster]

GO

:connect perFact\SQL2012 -U QODMaster -P takethepebblefrommyhand

create database QODTest

go

use QODTest

go

select * into MyDatabases from sys.databases

go

:connect perFact\SQL2012

alter authorization on database::QODTest to sa

go

:connect perFact\SQL2012 -U QODMaster -P takethepebblefrommyhand

use QODTest

go

select * from MyDatabases

go

:connect perFact\SQL2012

use tempdb

drop database QODTest

drop login QODMaster

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

Revenant

SSC-Forever

Points: 42467

January 23, 2014 at 1:38 pm

#1682925

Three points, hard earned... Thanks, Andy!