Improve company productivity with a Business Account.Sign Up


Problem dropping a (localDB)\v11.0 table programatically within a VS 2013 application.

Posted on 2015-02-04
Medium Priority
Last Modified: 2015-02-05
I created a (localdb)\v11.0 database in Visual Studio 2013 programmatically with Visual Basic.

Dim SQLcon as New SqlConnection
Dim SQLstr as string
Dim cmd1 As SqlCommand
Dim cmd2 As SqlCommand
Dim cmd3 As SqlCommand

gSQLcon.ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=;" + "Data Source=(LocalDB)\v11.0"


SQLstr = "CREATE DATABASE family ON PRIMARY" + "(Name=family, filename = '" + "c:\family.mdf', size=10," + "maxsize=20, filegrowth=10%)log on " + _
                                                              "(name=family_log, filename = '" + "c:\family.ldf',size=10," + "maxsize=20,filegrowth=1)"

cmd1 = New SqlCommand(SQLstr, gSQLcon)

The database was created successfully. I then created a table in the database.

Dim gSQLconFamily As New SqlConnection

gSQLconFamily.ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=family;" + "Data Source=(LocalDB)\v11.0""

SQLstr = "CREATE TABLE member (nmbr numeric(7, 0) NULL)"
cmd2 = New SqlCommand(SQLstr, gSQLconFamily)

The table was created successfully. I then attempted to drop the table created.

SQLstr = "DROP TABLE member"
cmd3 = New SqlCommand(SQLstr, gSQLconFamily)

The attempt to drop the table was unsuccessful and I got the following message:

"Cannot drop the table 'member', because it does not exist or you do not have permission."

The table existed and so the error must have been because I didn't have permission. Why would I not have permission to drop the table when I had just created it?
Question by:lschwabe
  • 2
LVL 25

Expert Comment

ID: 40588983
Do you have permission to drop the table? If not it won't allow it.

Author Comment

ID: 40589694
When the table is being created by an application can actions such as delete, update, select and insert be given to everyone?  When the table is opened in an application is there a connection string parameter that  would allow the current user to have all permissions?
LVL 25

Accepted Solution

SStory earned 2000 total points
ID: 40589844
You have to think in these terms.  It isn't permissions in the table you are creating, but permissions in the then thing that "owns" the table where you need the permission to delete (drop) an entire table. I suppose you could be able to create a table, but not drop it unless permissions are granted to do so.

You can allow a current user to have all permissions by granting that user all permissions in the DB. or logging in the dbo (not recommended).  If not the dbo, then the user, used must be granted the DROP privilege to that database. If this is inside an application then I could understand doing that if it needs to be able to drop a table at a whim. Of course that could wipe out all of your data when you didn't expect it perhaps.  I'd create the user, issue TSQL  GRANT commands for that user on the DB allowing creation of tables and dropping of them (DROP).  Or you might may two accounts. One that can create and delete the table during setup or uninstall and the other that cannot, but is used in day to day querying. It would be hard to say without knowing more about your application.

The user issuing the GRANT must have priv to do so.

Or perhaps if this is during install they have to know the dbo password to do so.

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question