Sql database permissions (or not?)

SQL Server Administration
1

I AM TRYING TO CREATE THE BACKUP FILES FOR A DATABASE THAT I HAVE BEEN USING OR MONTHS.:I AM RUNNING SQL SERVER EXPRESS 2014, AND VISUAL BASIC 2015 IN WINDOWS 10. IN SQL SERVER MANAGEMENT STUDIO I CAN RUN THIS SIMPLE COMMAND LIST AND IT RUNS PERFECT.

USE [D:\DATA FILES 092015\DVD_LIST.MDF]
SELECT * FROM [DVD_List]

BUT WHEN I TRY TO RUN THIS BACKUP SEQUENCE:

USE [D:\DATA FILES 092015\DVD_LIST.MDF]
ALTER DATABASE DVD_List SET RECOVERY FULL;
GO

BACKUP DATABASE DVD_List
TO DISK = 'F:\SQLServerBackups\DVD_ListfULLrm.bak'
WITH FORMAT;
GO

BACKUP LOG DVD_List
TO DISK = 'F:\SQLServerBackups\DVD_ListfULLrmlOG.bak';
GO

I GET THESE ERRORS:

Msg 5011, Level 14, State 5, Line 2
User does not have permission to alter database 'DVD_List', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Msg 911, Level 16, State 11, Line 5
Database 'DVD_List' does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 5
BACKUP DATABASE is terminating abnormally.
Msg 911, Level 16, State 10, Line 10
Database 'DVD_List' does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 10
BACKUP LOG is terminating abnormally.

HOW DO I GIVE MYSELF PERMISSION TO ALTER MY OWN DATABASE? BETTER YET, WHY DO I NEED TO GIVE MYSELF PERMISSION TO ALTER MY OWN DATABASE? AND THIRD, WHY DOES IT WORK IN THE FIRST SEQUENCE AND NOT THE SECOND? (PROBABLY BECAUSE IT DOESN’T CARE WHO LOOKS AT THE DATA BUT IS FUSSY ABOUT WHO ALTERS IT)

2

I've never seen this before. I'm assuming this is a new feature. It appears you have been accessing the database via the files and that the database has not been attached. Try attaching the database and then do the backups/alter.

But please note that the backup sequence you listed is unnecessary as there is nothing in the log yet since the log chain just started when you switched it to full. If your intention is to start the log chain, be sure to setup regular log backups, such as every few minutes.

4

TaraKizer
I'M NOT SURE WHAT YOU ARE SAYING. AS YOU CAN SEE IN THE SCREEN SHOTS, THE DATABASE IS ATTACHED, AND THE USE COMMAND IS ORCHESTRATED BY SSMS. AS SOON AS I TYPE IN THE "USE D" I GET AN INSET OFFERING ME THE OPTIONS AVAILABLE AND I JUST CLICKED ON THE CORRECT ONE AND IT IS TRANSFERRED TO THE QUERY. IT HAS BEEN THIS WAY EVER SINCE I STARTED USING SSMS. AS STATED IN THE ORIGINAL POST, WHEN I RUN THE QUERY I GET THE PERMISSIONS ERROR AND THE DATABASE DOES NOT EXIST ERROR. SO, WHY DON’T I HAVE THE PROPER PERMISSIONS, AND IF THE SIMPLE QUERY RUNS USING THE DVD_LIST.MDF WHY CAN’T THE BACKUP QUERY FIND THE SAME DATABASE?

1.jpg2550×3300 1.6 MB

5

According to those screen shots, your database is named "D:\DATA FILES 092015\DVD_LIST.MDF" and not DVD_LIST. That entire thing is what you named it rather than just DVD_LIST. You could rename it to DVD_LIST to make life easier. You can right click on the name to rename it.

6

The database base name is [D:\DATA FILES 092015\DVD_LIST.MDF]. A table name is DVD_List.

For db and log backups, you must use the db name, not a table name. Thus:

ALTER DATABASE [D:\DATA FILES 092015\DVD_LIST.MDF] SET RECOVERY FULL;
GO
BACKUP DATABASE [D:\DATA FILES 092015\DVD_LIST.MDF]
TO DISK = 'F:\SQLServerBackups\DVD_ListfULLrm.bak'
WITH FORMAT;
GO

BACKUP LOG [D:\DATA FILES 092015\DVD_LIST.MDF]
TO DISK = 'F:\SQLServerBackups\DVD_ListfULLrmlOG.bak';
GO

7

OK, i CHANGED THE DATABASE NAME TO DVD_LIST IN SSMS, AND THE BACKUP QUERY WORKED, BUT, NOW MY VISUAL BASIC PROGRAM CAN'T FIND IT. I GET AN ERROR "UNABLE TO OPEN THE PHYSICAL FILE 'D:\DATA FILES 092015\DVD_LIST.MDF' OPERATING SYSTEM ERROR 5 '5(ACCESS DENIED'"

i AM GETTING FRUSTRATED BY ALL THIS. WHEN I LOOK INTO THE VS EDITOR SERVER EXPLORER IT SHOWED THE DATAFILE, BUT NO ACCESS TO IT, I UNATTACHED IT AND TRIED TO REATTACH IT BUT IT SAYS IT DOESN'T EXIST. I TRIED JUST ATTACHING 'DVD_LIST' AS USED IN THE BACKUP QUERY BUT THAT DIDNT WORK EITHER.

I AM ENTERTAINING THE THOUGHT OF SHUTTING THIS THING DOwn, SITTING BACK IN MY LAZY BOY AND JUST WATCHING THE 625 MOVIES INSTEAD OF TRYING TO KEEP TRACK OF THEM : :wink:

8

I again suggest leaving the db name as it originally was, and just changing the db commands to use the appropriate db name. SQL doesn't care what the db name is anyway. [D:\DATA FILES 092015\DVD_LIST.MDF] is just a name string like [DVD_LIST] is a name string to SQL Server.

9

You need to change the connection string in your program to point to the DVD_LIST database name.

10

Scott;
I tried your way and it doesn't work. When I changed the name to DVD_LIST.mdf it did but then the program couldn't find the database. Once I changed everything back to where it started (ie: D:\Data Files 092015\DVD_List.mdf) the program ran OK, but not the Backup.

11

Tara;
Read the reply to Scott, in addition to that now (as of this morning) I couldn't get anything to work. The program gave me the "Cannot open the user default database. Login failed. Login failed for user DarbysTravelDen\Darby". SSMS showed the database, but when I ran a query it couldn't open the database. So I shut down everything but the computer and restarted SSMS,. The DVD_List database showed as "recovery pending" so I took the database offline and then brought it back online now everything works fine including the program. I wish there was a way I could show you these problems.

live:darbybrown

12

What specific error message(s) do you get when you run:

BACKUP DATABASE [D:\DATA FILES 092015\DVD_LIST.MDF]
TO DISK = 'F:\SQLServerBackups\DVD_ListfULLrm.bak' WITH FORMAT;

13

Today, None! The Backup seemed to run ok, adding to my confusion. I need to check t6he reliability of the backup file, but it seems to be ok.

14

If you were trying to do a backup(s) thru the SQL gui (SSMS), as a German friend of mine says, "better don't". It's just too flaky to be reliable for system tasks.

15

Tara;
Thanks for the help, your suggestions worked and all is 'almost' well. I still have the glitch of every once and a while my system doesn't recognize the SQL Server call and wont let me access the data file but after starting SSMS and running a query using the datafile the next time I run the MovieList program everything works fine. I think it's a system problem not a SQL problem. I'll figure that out in time.

BTW, I'm sorry for the delay between replies, I've had knee surgery and not handling it too well.

16

It sounds like the autoclose option is enabled. Right click on the database and go to properties. Disable autoclose.