Solved

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

Posted on 2015-02-04
3
384 Views
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"

gSQLcon.Open()

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)
cmd1.ExecuteNonQuery()


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)"
gSQLconFamily.Open()
cmd2 = New SqlCommand(SQLstr, gSQLconFamily)
cmd2.ExecuteNonQuery()


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

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


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?
0
Comment
Question by:lschwabe
  • 2
3 Comments
 
LVL 25

Expert Comment

by:SStory
Comment Utility
Do you have permission to drop the table? If not it won't allow it.
0
 

Author Comment

by:lschwabe
Comment Utility
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?
0
 
LVL 25

Accepted Solution

by:
SStory earned 500 total points
Comment Utility
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.
https://msdn.microsoft.com/en-us/library/ms178569.aspx

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
The purpose of this article is to demonstrate how we can use conditional statements using Python.
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…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

744 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now