MySQL – Preserve Execute Permissions for Procedure with ALTER PROCEDURE

The best thing you can do to understand this behaviour better is to ask the software vendor. If you don't want to grant those permissions, lodging a bug report might be appropriate.

I'm going to hazard a guess that EMS SQL Manager is trying to be too clever, and check whether you have Alter_routine_priv or Create_routine_priv so that it can return a warning or error before it tries sending your ALTER ROUTINE to the database.

Most SQL Editors run lots of queries in the background to support "IntelliSense", speed up common queries, or provide background information, and a lot of the time they just don't make any sense (some of them are downright frightening).

First you have to

• Create Login with create database permission. Note that the login cannot drop any databases.

  USE [master]
  GO
  
  CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
  DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  GO
  use [master]
  GO
  -- Grant create any database, but not drop or alter any database
   GRANT CREATE ANY DATABASE TO [Kin]
  GO
  

• Map the login to the database that you want to have permission to alter or drop.

  -- for drop database, you just need db_owner
  USE [test_drop]
  GO
  CREATE USER [Kin] FOR LOGIN [Kin] 
   GO
  

• Add the user to the db_owner role.

    EXEC sp_addrolemember 'db_owner', 'Kin'
  

Now test it for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop


EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))


EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin] 
set online

Below is the error :

Msg 5011, Level 14, State 9, Line 2 User does not have permission to alter database 'test_kin', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed.