Jul 20, 2011 at 10:32 PM
Edited Jul 20, 2011 at 10: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?
object_name(dbperm.major_id) as ObjName
,dbprincipal.name as PrincipalName
join sys.database_principals dbprincipal on dbperm.grantee_principal_id = dbprincipal.principal_id
Jul 21, 2011 at 12:55 AM
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.