Lost EXEC Permissions

Jul 20, 2011 at 9:32 PM
Edited Jul 20, 2011 at 9:33 PM

I've migrated an on-premise DBMS via SQL Migration Wizard 3.7.4 to Azure.  The on-premise DBMS has various Roles that themselves have EXEC permissions to the appropriate stored procedures.

Once migration is complete, all our stored procedures lack any permissions ... and thus none of our T-SQL users can call them.

From ADO.NET, we get error "The EXECUTE permission was denied on the object" to all the stored procedures. 
When I run the following query below on my on-premise database, prior to migration, I get many rows.  When I run it on Azure, within my migrated DBMS, I get 0 rows.

Did I do something wrong, or are we dropping permissions on the floor?

 

  

 

 

select 
 object_name(dbperm.major_id) as ObjName
 ,dbprincipal.name as PrincipalName
 ,dbperm.*
from
 sys.database_permissions dbperm
 join sys.database_principals dbprincipal on dbperm.grantee_principal_id = dbprincipal.principal_id
where 
 substring(object_name(dbperm.major_id),1, 3)='usp'
order by 
 ObjName,
 dbprincipal.name
 
Coordinator
Jul 20, 2011 at 11:55 PM

Hi Howard,

 

No, you didn’t do anything wrong.  Security is one thing that SQLAzureMW does not handle.  Once you have all of your objects migrated, you will have to setup the security by hand.

 

Sorry,

George