Deprecated warning message is throwing an exception

Question

We have a legacy application that uses a REVOKE ALL SQL command. Up until now, all of our customers can run the application under SQL 2005 and SQL 2008 without any errors. We have one customer that has been running our application for years without any problems. Recently, they have been starting to receive errors in the form of an exception when they run the app and execute the above command. the error states "The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity." My first impression was that they must have upgraded their SQL instance to one that no longer supports the deprecated command. Usually, these type of low level warnings never get thrown as errors. I checked their SQL 2005 version and it's 9.0.3073. We are running the following versions in house but do not receive the error. (9.0.3042, 9.0.5000, and 10.0.2531). If I open SQL Server Management Studio and run the command under a new query, I do indeed see the warning message but it does not get displayed when executed from our app at our location, connected to our SQL server instances.

Answer 1

@jclements7396,

Could you please post the related code in your legacy application?

 "The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity." is a deprecation warning. The grant still works, for now, but ALL will be removed in a future version of SQL Server.

In SQL Server 2005, we have introduced new permissions. ALL does not include these new permissions (otherwise, existing programs might end up running with higher permissions in SQL Server 2005 than they did in SQL Server 2000). So the warning is for letting you know that ALL doesn't really mean "all permissions" anymore.

You should replace GRANT ALL with specific grants of the permissions you want to GRANT, for example, in your case, you can just replace GRANT ALL with GRANT EXECUTE, as EXECUTE is the only permission implied by ALL on stored procedures.

Please refer to this article for more details.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Below is the procedure that is causing the exception. The exception gets triggered on the execute statement at the bottom.

procedure TFormGroupRights.UpdateGroupRights(TableName: String;

             RightToGrant: SmallInt;DBObject:TDBObject);

var

   CommandString: String;

   strTableName,gname: string;

   No: integer;

begin

   CommandString := '';

   //Check if table , view or stored proc exists in the database by checking the view

   //DATABASEOBJECTNAMES

   No := Pos('.',TableName);

   strTableName := Copy(TableName,No+1,Length(TableName));//Strip out the

   case DatabaseType of

      dbtOracle: CommandString := 'REVOKE ALL ON ' + TableName + ' FROM ' +

                                              GroupName;

      dbtMSSQL : CommandString := 'REVOKE ALL ON ' + TableName + ' TO ' +

                                               GroupName;

      dbtSybase:

   //For SQL Anywhere, you can't revoke unless you have granted previously.

   //Check if any right has been granted .

  begin

      if DBObject = dboSP then  //Stored Procedure

        begin

          Rights_DM.QuerySybProc.Close;

          No := Pos('.',TableName);

          strTableName := Copy(TableName,No+1,Length(TableName));

          Rights_DM.QuerySybProc.Parameters.ParamValues['GroupName'] := GroupName;

          Rights_DM.QuerySybProc.Parameters.ParamValues['ProcName'] := strTableName;

          Rights_DM.QuerySybProc.Open;

          if Rights_DM.QuerySybProc.Fields[0].AsInteger > 0 then

            CommandString := 'REVOKE EXECUTE ON ' +TableName + ' FROM ' + GroupName;

          Rights_DM.QuerySybProc.Close;

        end

      else

        begin  //Table or view

          No := Pos('.',TableName);

          strTableName := Copy(TableName,No+1,Length(TableName));

          Rights_DM.QuerySybTable.Close;

          Rights_DM.QuerySybTable.Parameters.ParamValues['GroupName'] := GroupName;

          Rights_DM.QuerySybTable.Parameters.ParamValues['TableName'] := strTableName;

          Rights_DM.QuerySybTable.Open;

          if Rights_DM.QuerySybTable.Fields[0].AsInteger > 0 then

            CommandString := 'REVOKE ALL ON ' + TableName + ' FROM ' + GroupName;

         Rights_DM.QuerySybTable.Close;

        end;

     end;

  end;

 gname := GroupName;

 if Length(CommandString) > 0 then

 begin

   Rights_DM.ADOCommandRights.CommandText := CommandString;

   Rights_DM.ADOCommandRights.Execute;

 end;

Answer 3

did you get any solution for this?

Answer 4

Not Yet. I am currently in the process of installing a SQL 2008 Express instance along side the 2005 express instance. I intend to backup the database and restore to the SQL 2008 instance.

Answer 5

I completed the install of a new instance of SQL 2008 Express. I backed up the database running under SQL 2005 Express, restored it on the SQL 2008 Express instance and I am still getting the exception.

Answer 6

Solved the problem. The REVOKE ALL command above was being applied to a table for a role that had "With grant" privileges. When "With Grant" priveleges are present and you try to apply the REVOKE ALL command, in addition to the deprecated warning, you also receive the error "To revoke or deny grantable privileges, specify the CASCADE option". When run from SQL Server Management Studio, you see both the warning and the error. However, when this command was executed within our application, the ADO object was only reporting the warning message.