[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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

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?
  • 2
1 Solution
Do you have permission to drop the table? If not it won't allow it.
lschwabeAuthor Commented:
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?
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now