I have some users in my database. They are member of db_datareader. They now also need to have execute permissions on stored procedures. I was just wondering what would happen if one of the stored procedures issue DDL? Would they be able to make schema changes to tables, drop etc. through that stored procedure?
Thank you
NO, With just Execute permission doesn't allow a user to execute a stored procedure successfully if it contains some DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME) statements.
Use module signing. Sign your procedures with a certificate and then grant the required permissions to a certificate-derived user. The link contains a full example.
