Results 1 to 2 of 2

Thread: modify a view

  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: modify a view

    The request is to modify the view to limit access to all but this one table it exists in.. (the idea is to use the view as a security filter)
    and would it be possible to write the code in the view that will create a separate field for the date proper and the time, so there are 2 fields:
    Request_Date
    Request_Time


    any help much appreciated!!!!

    Code:
    USE [campaign]
    GO
    /****** Object:  View [dbo].[Membership_View]    Script Date: 06/20/2008 13:37:56 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[Membership_View]
    AS
    SELECT     Contact_LName, Contact_Parish, Contact_City, Contact_State, Contact_Zip, Contact_Country, Request_Comments, Contact_Gender, 
                          Contact_Membership, Contact_Practicing, Request_Language, Request_ReferralSource, Request_SendBy, Request_URL, Request_Date, 
                          Contact_PhoneTime, Request_IsAccepted, Request_RejectedReason, Admin_DateContacted, Admin_DateFirstDegree, Admin_DateUpdated, 
                          Admin_Status, Membership_Campaign_ID
    FROM         dbo.Membership_Campaign
    
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
    Begin DesignProperties = 
       Begin PaneConfigurations = 
          Begin PaneConfiguration = 0
             NumPanes = 4
             Configuration = "(H (1[46] 4[35] 2[4] 3) )"
          End
          Begin PaneConfiguration = 1
             NumPanes = 3
             Configuration = "(H (1 [50] 4 [25] 3))"
          End
          Begin PaneConfiguration = 2
             NumPanes = 3
             Configuration = "(H (1 [50] 2 [25] 3))"
          End
          Begin PaneConfiguration = 3
             NumPanes = 3
             Configuration = "(H (4 [30] 2 [40] 3))"
          End
          Begin PaneConfiguration = 4
             NumPanes = 2
             Configuration = "(H (1 [56] 3))"
          End
          Begin PaneConfiguration = 5
             NumPanes = 2
             Configuration = "(H (2 [66] 3))"
          End
          Begin PaneConfiguration = 6
             NumPanes = 2
             Configuration = "(H (4 [50] 3))"
          End
          Begin PaneConfiguration = 7
             NumPanes = 1
             Configuration = "(V (3))"
          End
          Begin PaneConfiguration = 8
             NumPanes = 3
             Configuration = "(H (1[56] 4[18] 2) )"
          End
          Begin PaneConfiguration = 9
             NumPanes = 2
             Configuration = "(H (1 [75] 4))"
          End
          Begin PaneConfiguration = 10
             NumPanes = 2
             Configuration = "(H (1[66] 2) )"
          End
          Begin PaneConfiguration = 11
             NumPanes = 2
             Configuration = "(H (4 [60] 2))"
          End
          Begin PaneConfiguration = 12
             NumPanes = 1
             Configuration = "(H (1) )"
          End
          Begin PaneConfiguration = 13
             NumPanes = 1
             Configuration = "(V (4))"
          End
          Begin PaneConfiguration = 14
             NumPanes = 1
             Configuration = "(V (2))"
          End
          ActivePaneConfig = 0
       End
       Begin DiagramPane = 
          Begin Origin = 
             Top = 0
             Left = 0
          End
          Begin Tables = 
             Begin Table = "Membership_Campaign"
                Begin Extent = 
                   Top = 0
                   Left = 4
                   Bottom = 220
                   Right = 667
                End
                DisplayFlags = 280
                TopColumn = 24
             End
          End
       End
       Begin SQLPane = 
       End
       Begin DataPane = 
          Begin ParameterDefaults = ""
          End
       End
       Begin CriteriaPane = 
          Begin ColumnWidths = 11
             Column = 1440
             Alias = 900
             Table = 1170
             Output = 720
             Append = 1400
             NewValue = 1170
             SortType = 1350
             SortOrder = 1410
             GroupBy = 1350
             Filter = 1350
             Or = 1350
             Or = 1350
             Or = 1350
          End
       End
    End
    ' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'Membership_View'
    
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'Membership_View'

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select	dateadd(day, datediff(day, 0, getdate()), 0) as DateOnly
    select	dateadd(day, -datediff(day, 0, getdate()), getdate()) as TimeOnly
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •