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...
>
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> 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

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,
Terrell
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.
----
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...
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> 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

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

Permissions

Hi:
Actually I have a store proc which pulls data from a table. I have given
execute permission to a user on this store proc. Since owner of store proc
and table are the same (dbo), I do not need to give select permission on the
table for this user.
But i am having problem when I use dynamic sql to build a query string and
execute this sql. It then gives an error that permission denied on this
table for this user. I think It starts a new session when you build a
querystring (Select * from Table), it checks for permission on that table
because the ownership chain breaks.
If you know of any workaround to this problem, please let me know becuase I
do not want to give select permission for this user. It should be executed
thru the stored proc.
ThanksYou will need to re-write the stored procedure to not use dynamic SQL,
which requires the user to have permissions on the base tables.
--Mary
On Thu, 12 Aug 2004 14:09:03 -0700, Sal
<Sal@.discussions.microsoft.com> wrote:

>Hi:
>Actually I have a store proc which pulls data from a table. I have given
>execute permission to a user on this store proc. Since owner of store proc
>and table are the same (dbo), I do not need to give select permission on th
e
>table for this user.
>But i am having problem when I use dynamic sql to build a query string and
>execute this sql. It then gives an error that permission denied on this
>table for this user. I think It starts a new session when you build a
>querystring (Select * from Table), it checks for permission on that table
>because the ownership chain breaks.
>If you know of any workaround to this problem, please let me know becuase I
>do not want to give select permission for this user. It should be executed
>thru the stored proc.
>Thanks|||Sal,
There is one work-around I have found. If you are trying to insert
data into a permanent table via dynamic sql, first create a temporary
table with the same structure as the destination permenant table.
Insert into the temporary table via the dynamic statement.
Then, with non-dynamic sql, insert from the temp table into the
permanent table.
Hope this helps.
Sal wrote:
> *Hi:
> Actually I have a store proc which pulls data from a table. I have
> given
> execute permission to a user on this store proc. Since owner of
> store proc
> and table are the same (dbo), I do not need to give select permission
> on the
> table for this user.
> But i am having problem when I use dynamic sql to build a query
> string and
> execute this sql. It then gives an error that permission denied on
> this
> table for this user. I think It starts a new session when you build
> a
> querystring (Select * from Table), it checks for permission on that
> table
> because the ownership chain breaks.
> If you know of any workaround to this problem, please let me know
> becuase I
> do not want to give select permission for this user. It should be
> executed
> thru the stored proc.
> Thanks *
lxstewart
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message952424.html