Solved

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

Posted on 2015-02-04
3
406 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
ID: 40588983
Do you have permission to drop the table? If not it won't allow it.
0
 

Author Comment

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

Accepted Solution

by:
SStory earned 500 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.
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
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.

831 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