Thursday, March 29, 2012

do not have permission error message

I have been trying to run a script that modifies objects in a SQL Server 2005 database. I am trying to drop an index but I am told the index does not exist or I do not have the correct permissions. An example of one of the commands in the script that I am trying to execute are as follows.

command:
DROP INDEX spices.ADAPTER_MESSAGE.ADAPTER_MESSAGE_SET;

response:
Msg 3701, Level 11, State 6, Line 1
Cannot drop the index 'spices.ADAPTER_MESSAGE.ADAPTER_MESSAGE_SET', because it does not exist or you do not have permission.

The index does exist in the table so the problem must be related to permissions. I am the one who created the database so I am the dbo. I should have permission to do anything in the database, right? So why am I getting this error when my script runs?

thanks,
shimo

The dbo should be able to drop the index.

Is it possible that you are not running as the dbo? Maybe an application role has been set or something like that.

One way to check is

select user_name() to determine your user context.

Another possiblility is that the spices table is not in dbo or your default schema.

Try using the two part name schema.spices.ADAPTER_MESSAGE.ADAPTER_MESSAGE_SET

HTH,

-Steven Gott

S/DET

SQL Server

|||

Is this a SQL x64 bit edition?

We have had similar issue and reported to MS Connect site.

sql

No comments:

Post a Comment