permissions resetting on a View

I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
TerrellWhen you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell|||>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx.gbl...
> >--Original Message--
> >When you delete a Role or a View, or any other object for
> that matter, you
> >also delete the permissions associated with those
> objects. If you what to
> >keep the permissions for the View then don't delete the
> view and recreate,
> >but instead just ALTER the view. When you ALTER an
> object the permissions
> >that are associated with the object stay intact.
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell