Re: permissions resetting on a View
From: Gregory A. Larsen (greg.larsen_at_netzero.com)
Date: 07/12/04
- Next message: Ivan Mckenzie: "Is MS03-031 needed on SQL2000 SP3?"
- Previous message: Norman: "RE: master.dbo.xp_cmdshell is failing"
- In reply to: Terrell Miller: "Re: permissions resetting on a View"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 12 Jul 2004 14:20:23 -0700
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
- Next message: Ivan Mckenzie: "Is MS03-031 needed on SQL2000 SP3?"
- Previous message: Norman: "RE: master.dbo.xp_cmdshell is failing"
- In reply to: Terrell Miller: "Re: permissions resetting on a View"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
