LuckyLucks
asked on
MS SQL server 2012 query
Hi:
I am looking to drop and recreate a view, if its exists, and Grant a role to it. I have the skeleton below that doesn't work, could you advise me of the correction?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
DROP VIEW [dbo].[myView];
CREATE VIEW [dbo].[myView]
AS
SELECT
COl1,
COl2
FROM Table T
GO
GRANT ROLE myROLE to [dbo].[myView]
I am looking to drop and recreate a view, if its exists, and Grant a role to it. I have the skeleton below that doesn't work, could you advise me of the correction?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
DROP VIEW [dbo].[myView];
CREATE VIEW [dbo].[myView]
AS
SELECT
COl1,
COl2
FROM Table T
GO
GRANT ROLE myROLE to [dbo].[myView]
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Correct. That way it drops only if it already exists, potentially avoiding a 'you tried to drop a view that does not exist' error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
IF EXISTS (SELECT name FROM sys.views WHERE name='myView')
DROP VIEW [dbo].[myView]
GO
CREATE VIEW [dbo].[myView]
AS
blah
GO