I have a windows application that accesses a SQLServer database. Sometime within the last week or so it stopped working on other peoples machines with an exception thrown indicating they did not have execute permissions on a stored procedure. I am wondering what mechanism might have changed my database (certainly I did not do anything . . . at least knowingly).
I have setup a role called db_execproc, added users (the application connects using windows authentication), and granted the role EXECUTE on the dbo schema. Everything was working fine, but for some reason it stopped working. Granting EXECUTE again to that role fixed the problem.
So I am wondering how this might have been broken (SQLServer update?).
Also, in the SQL Server Management Studio I was trying to verify that db_execproc had the right permissions, but I cant see anything (either before I granted them or after) . . . how can I see what permissions have been granted to a role?