SQL Server 2005 Losing Permissions

Question

We're using SQL Server 2005 as a DB for our website. The user account that is being used to access the DB from the website is losing certain permissions once a day or once every 2 days.

The role is still in SQL, but it is losing it's "data reader" permission on it's own.

What could be causing this? How can I prevent it?

Answer 1

The user is being one of:

• dropped and recreated from the DB
• removed from the role

I've never known permissions to be lost without code written by someone doing it.

I'd run a profiler trace with some security events set to see who is doing it

Edit: SQL Server 2000 had a bug that dropped SELECT rights on multi statement table valued UDFs. Not in SQL Server 2005.