SQL Server Database Role Losing permissions

mossmis used Ask the Experts™
We recently went live on a SQL server 2008 database. I have created a SQL server user and that user has been granted permissions to a database role I created. For some reason, the views I granted this role permissions to select keeps disappearing the next day. I have to keep re-adding it everyday for the my custom app to work. Anyone have any idea why they would be disappearing?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professional Troublemaker^h^h^h^h^hshooter
Is there any chance that some process is dropping and re-creating the view?

(You might be able to pull 'Standard Reports', 'Schema Changes History' -- and see if the view is being dropped and recreated.)


Razmus, I looked at the report. Nothing points directly to the views in question, but I have a feeling what you say might be happening. I will probably rewrite my own view outside of that database to possibly avoid this. I have a couple of different databases on the same server and they talk to each other. That is why I have the role to grant certain tables/view for a user that has limited access.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial