Make Changes to a column which is part of replication and that table, Column is used in Indexed Views and that index is Schema bound also how to add normal view to replication

USE Database
go
SELECT * from syspublications
GO

USE Database
go
EXEC sp_dropsubscription
'Database_views_pub','SA2','SERVER4','Database'
go

USE Database
go
EXEC sp_dropsubscription
'Database_views_pub','vwCP','SERVER4','Database'
GO

---Taking out Views
USE Database
go
EXEC sp_droparticle
@publication = 'Database_views_pub',
@article = 'SA2',
@force_invalidate_snapshot = 1;
GO

USE Database
go
EXEC sp_droparticle
@publication = 'Database_views_pub',
@article = 'vwCP',
@force_invalidate_snapshot = 1;
GO

GO and drop views at SERVER4, otherwise it wont work


--Adding Back
use Database
go
EXEC sp_changepublication
@publication = 'Database_views_pub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'Database_views_pub',
@property = N'immediate_sync',
@value = 'false'
GO

EXEC sp_addarticle  @publication = N'Database_views_pub',
@article = N'SA2',---this is indexed view in publisher
@source_object=N'SA2',

@Type='indexed view schema only'
GO

EXEC sp_addarticle  @publication = N'Database_views_pub',
@article = N'vwCP',--this is not indexed view
@source_object=N'vwCP',
@Type='view schema only'
GO

--Should run on publisher
EXEC sp_refreshsubscriptions @publication = N'Database_views_pub'
GO
  ----Should run on publisher
EXEC sp_startpublication_snapshot @publication = N'Database_views_pub'
go

----MUST
--enable back

EXEC sp_changepublication
@publication = 'Database_views_pub',
@property = N'immediate_sync',
@value = 'true'
GO
EXEC sp_changepublication
@publication = 'Database_views_pub',
@property = N'allow_anonymous',
@value = 'True'
GO

--Check on subscriber

Missing log Table for trigger and application breaks

Hi,

I had a situation in my work recently where i had a trigger ON a Account Table and whenever a account is created, modified, deleted (record inserted) i wanted to to insert into a log table for auditing , tested in stage all good worked good for weeks


we finally decided to move to production,
Moved the trigger definition but forgot to move the LOG table and Application didn't work .


created the LOG table and everything worked as expected .

who got deny Access to Objects in SQL Server

 

SELECT pr.type_desc, pr.name, pe.state_desc,
pe.permission_name, s.name + '.' + oj.name AS Object, major_id
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
  ON pr.principal_id = pe.grantee_principal_id
JOIN sys.objects AS oj
  ON oj.object_id = pe.major_id
JOIN sys.schemas AS s
  ON oj.schema_id = s.schema_id
WHERE   state_desc like '%DENY%'


OR










SELECT CASE WHEN P.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE P.state_desc END AS cmd_state,
       P.permission_name,
       'ON '+ CASE P.class_desc
           WHEN 'DATABASE' THEN 'DATABASE::'+QUOTENAME(DB_NAME())
           WHEN 'SCHEMA' THEN 'SCHEMA::'+QUOTENAME(S.name)
           WHEN 'OBJECT_OR_COLUMN' THEN 'OBJECT::'+QUOTENAME(OS.name)+'.'+QUOTENAME(O.name)+
             CASE WHEN P.minor_id= 0 THEN '('+QUOTENAME(C.name)+')' ELSE '' END
           WHEN 'DATABASE_PRINCIPAL' THEN
             CASE PR.type_desc
               WHEN 'SQL_USER' THEN 'USER'
               WHEN 'DATABASE_ROLE' THEN 'ROLE'
               WHEN 'APPLICATION_ROLE' THEN 'APPLICATION ROLE'
             END +'::'+QUOTENAME(PR.name)
           WHEN 'ASSEMBLY' THEN 'ASSEMBLY::'+QUOTENAME(A.name)
           WHEN 'TYPE' THEN 'TYPE::'+QUOTENAME(TS.name)+'.'+QUOTENAME(T.name)
           WHEN 'XML_SCHEMA_COLLECTION' THEN 'XML SCHEMA COLLECTION::'+QUOTENAME(XSS.name)+'.'+QUOTENAME(XSC.name)
           WHEN 'SERVICE_CONTRACT' THEN 'CONTRACT::'+QUOTENAME(SC.name)
           WHEN 'MESSAGE_TYPE' THEN 'MESSAGE TYPE::'+QUOTENAME(SMT.name)
           WHEN 'REMOTE_SERVICE_BINDING' THEN 'REMOTE SERVICE BINDING::'+QUOTENAME(RSB.name)
           WHEN 'ROUTE' THEN 'ROUTE::'+QUOTENAME(R.name)
           WHEN 'SERVICE' THEN 'SERVICE::'+QUOTENAME(SBS.name)
           WHEN 'FULLTEXT_CATALOG' THEN 'FULLTEXT CATALOG::'+QUOTENAME(FC.name)
           WHEN 'FULLTEXT_STOPLIST' THEN 'FULLTEXT STOPLIST::'+QUOTENAME(FS.name)
           WHEN 'SEARCH_PROPERTY_LIST' THEN 'SEARCH PROPERTY LIST::'+QUOTENAME(RSPL.name)
           WHEN 'SYMMETRIC_KEYS' THEN 'SYMMETRIC KEY::'+QUOTENAME(SK.name)
           WHEN 'CERTIFICATE' THEN 'CERTIFICATE::'+QUOTENAME(CER.name)
           WHEN 'ASYMMETRIC_KEY' THEN 'ASYMMETRIC KEY::'+QUOTENAME(AK.name)
         END COLLATE Latin1_General_100_BIN AS securable,
         'TO '+QUOTENAME(DP.name) AS grantee,
         CASE WHEN P.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'WITH GRANT OPTION' ELSE '' END AS grant_option,
         'AS '+QUOTENAME(G.name) AS grantor
  FROM sys.database_permissions AS P
  LEFT JOIN sys.schemas AS S
    ON P.major_id = S.schema_id
  LEFT JOIN sys.all_objects AS O
       JOIN sys.schemas AS OS
         ON O.schema_id = OS.schema_id
    ON P.major_id = O.object_id
  LEFT JOIN sys.types AS T
       JOIN sys.schemas AS TS
         ON T.schema_id = TS.schema_id
    ON P.major_id = T.user_type_id
  LEFT JOIN sys.xml_schema_collections AS XSC
       JOIN sys.schemas AS XSS
         ON XSC.schema_id = XSS.schema_id
    ON P.major_id = XSC.xml_collection_id
  LEFT JOIN sys.columns AS C
    ON O.object_id = C.object_id
   AND P.minor_id = C.column_id
  LEFT JOIN sys.database_principals AS PR
    ON P.major_id = PR.principal_id
  LEFT JOIN sys.assemblies AS A
    ON P.major_id = A.assembly_id
  LEFT JOIN sys.service_contracts AS SC
    ON P.major_id = SC.service_contract_id
  LEFT JOIN sys.service_message_types AS SMT
    ON P.major_id = SMT.message_type_id
  LEFT JOIN sys.remote_service_bindings AS RSB
    ON P.major_id = RSB.remote_service_binding_id
  LEFT JOIN sys.services AS SBS
    ON P.major_id = SBS.service_id
  LEFT JOIN sys.routes AS R
    ON P.major_id = R.route_id
  LEFT JOIN sys.fulltext_catalogs AS FC
    ON P.major_id = FC.fulltext_catalog_id
  LEFT JOIN sys.fulltext_stoplists AS FS
    ON P.major_id = FS.stoplist_id
  LEFT JOIN sys.registered_search_property_lists AS RSPL
    ON P.major_id = RSPL.property_list_id
  LEFT JOIN sys.asymmetric_keys AS AK
    ON P.major_id = AK.asymmetric_key_id
  LEFT JOIN sys.certificates AS CER
    ON P.major_id = CER.certificate_id
  LEFT JOIN sys.symmetric_keys AS SK
    ON P.major_id = SK.symmetric_key_id
  JOIN sys.database_principals AS DP
    ON P.grantee_principal_id = DP.principal_id
  JOIN sys.database_principals AS G
    ON P.grantor_principal_id = G.principal_id
 where P.state_desc like'%deny%'

dbo orphaned user

After I Restore SQL database from Production to Stage, Application was unable to connect and I saw dbo as orphan user .


Fix:Changed the database owner to SA fixed.

Logon failed for login 'ABC' due to trigger execution.

All of a sudden developers complained about issue.

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Logon failed for login 'ABC' due to trigger execution.

Changed database context to 'XYZ'.

Changed language setting to us_english.

Solution:

I DISABLED TRIGGER. 

Help:

http://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/

I used below trigger to test and find out login information .

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [Trigger_ServerLogon]

ON ALL SERVER FOR LOGON

AS

BEGIN

DECLARE @data XML

SET @data = EVENTDATA()

INSERT INTO dbo.ServerLogonHistory

SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

, @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')

, @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)')

END

GO

DISABLE TRIGGER [Trigger_ServerLogon] ON ALL SERVER

GO

Hi, I need a small tip from experts,

I have a table like below with

ABC( id int,datecreated datetime, CC Varchar(20),Accountnumber(20), name varchar(10), OldRecord XML, Newrecord XML)

I have some thousands of records in ABC table, 

I have data something like below

Select * from ABC

 id datecreated        CC      Accountnumber        name                   OldRecord                                                            Newrecord

1   2016-07-11     null       #4%&*@))@1       NICK                 <row cc='1234' name='nick'  date='2016-07-11'>           null 

now, i would like to search data from Oldrecord column or new record column which are XML data type columns 

like below:

SELECT * from ABC

where Oldrecord like '%Nick%' 

or 

SELECT * from ABC

where Oldrecord like '%CC=1234%' 

i tried running the queries above and i got below error:

Argument data type xml is invalid for argument 1 of like function.

Please help me on how to search data in XML column data type 

Answer:

SELECT Top 10 * from  [dbo].[AuditLog] with(nolock)

where  cast(NewRecord as nvarchar(max)) like '%cc=1234%'