Database Role seemingly lost permission

Question

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?

Answer 1

So I am wondering how this might have been broken (SQLServer update?).

SQL Server update wont change any login / user or role permissions as part of update (installing CUs or SPs).

The permissions might have changed due to a possible database upgrade (your application version upgrade). Check your upgrade scripts.

To find out who changed the permissions, you need to have a server side trace running and collecting relevant information.

how can I see what permissions have been granted to a role?

You can use this TSQL to check permissions granted to a role.