I have a visual basic application which needs to make some changes to a SQL Server database (either SQL Server 2005 or 2008). Specifically, I am trying to eliminate a schema (e.g. MySchema) from the database altogether. The first thing I must
do is move all database objects from 'MySchema' to 'dbo'. To that end, I execute the command:
ALTER SCHEMA dbo TRANSFER MySchema.ActionsCompleted
I am using an ODBC connection to the database. There is a user name and password setup on the server that my application uses for all database access. The error message I get is:
Cannot find the object 'ActionsCompleted', because it does not exist or you do not have permission.
When I run this exact statement from a query in SQL Server Mgmt Studio, it works fine. The difference is the user (and therefore the permissions). When I execute from the SQL Server Mgmt Studio, I am effectively an administrator, from my application,
I am not.
So, I am trying to figure out what permissions I need to give my application to execute this line. The documentation says that the user my have CONTROL priviledges over the securable (in this case the table called MySchema.ActionsCompleted) and
ALTER priveledges on the dbo schema. OK, so next I try the following (in SQL Server MGMT Studio):
GRANT ALTER ON
View Complete Post