May 19, 2008 at 8:01 am
Hello, have a problem on a SQL 2000 server I have a local db user that has permisson on
one database and one view but the problem is that the permission keeps getting lost.
I have to reapply the permissions 2-3 time per month! The database and the view er used
on a daly bases.
I have no idea whats happening, hope that anyone can help.
Thanks in advance - Jamm
May 19, 2008 at 8:15 am
other than a database restore , only way this can happen is if someone\something in app removes rights. Use profiler to monitor for event security\audit object permissons event.
Also - if object is dropped and recreated ever, permissions to it will be lost, so use alter view rather than drop\create
---------------------------------------------------------------------
May 19, 2008 at 8:38 am
As mentioned above, someone must be changing things. SQL server doesn't alter permissions for any reason.
May 19, 2008 at 8:52 am
Your most likely culprit is that this database is being restored from another copy somewhere (restoring Prod onto Development once per week to refresh data, for example).
That will alter the permissions. If that is the case, you may consider adding a step to the end of the restore job (assuming this is done by Agent job/SSIS package, as opposed to manually) that re-assigns the appropriate permissions. We have several instances of this scenario in our development system.
"Got no time for the jibba jabba!"
-B.A. Baracus
May 19, 2008 at 10:43 am
Hello, Thank you all for the reply.
The Database is not beining restored this is very strange.
May be it could be some app, I will look and see and also I will try the profiler.
Thanks - Jamm
May 19, 2008 at 3:16 pm
Does any process recreate the view on a regular basis? You can check it by a query like the following:
SELECT NAME, create_date, modify_date
FROM sys.objects
WHERE NAME = '** View Name **'
As far as losing permission to the database, do you mean you have to regard access just to access the database on a regular basis? Or do you mean permissions within the database, such as the on the view, are lost?
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply