· 8 years ago · Feb 28, 2018, 11:50 AM
1ALTER PROCEDURE DenyIUD --'shared'
2@repliName nvarchar(100) = null
3AS
4--exec DenyIUD 'test2'
5--exec DenyIUD
6BEGIN
7
8---################replication objects###########################--
9 --checks if table exsist-------
10 IF OBJECT_ID('tempdb.dbo.#repliObject', 'U') IS NOT NULL
11 DROP TABLE #repliObject ;
12
13CREATE TABLE #repliObject
14(
15id int
16,artn varchar (200)
17,bo varchar (200)
18,do varchar (200)
19,so varchar (200)
20,t int
21,s int
22,f varchar (200)
23,descr varchar (200)
24,ic varchar (200)
25,uc varchar (200)
26,dc varchar (200)
27,csp varchar (200)
28,vp int
29,pci int
30,fc varchar (200)
31,scho varchar (200)
32,desto varchar (200)
33,souo varchar (200)
34,unqua varchar (200)
35,suncoo varchar (200)
36,uso varchar (200)
37,fo varchar (200)
38,uf varchar (200)
39,air int
40,pir varchar (200)
41,iran varchar (200)
42,thresg varchar (200)
43,idenr int
44,fto int
45)
46
47if @repliName is not null
48 BEGIN
49 insert into #repliObject
50 exec sp_helparticle @publication= @repliName
51
52 --select bo from #repliObject
53 END
54---################################################################--
55
56 --checks if table exsist-------
57 IF OBJECT_ID('tempdb.dbo.#usersToReplicate', 'U') IS NOT NULL
58 DROP TABLE #usersToReplicate ;
59
60 CREATE TABLE #usersToReplicate (
61 name varchar(100)
62 );
63
64 INSERT INTO #usersToReplicate (name)
65 VALUES
66 ('gkowal')
67
68
69DECLARE @sqlcommand nvarchar(max)
70DECLARE @replicatedTableCur nvarchar(max)
71DECLARE @usersCur nvarchar(200)
72
73DECLARE replicatedTableCur CURSOR FOR
74 --select replicated tables names********-
75 SELECT
76 OBJECT_NAME(p.object_id) AS [Table]
77 FROM sys.partitions p
78
79 INNER JOIN sys.indexes i
80 ON p.object_id = i.object_id
81 AND p.index_id = i.index_id
82 INNER JOIN SYS.TABLES S
83 ON S.object_id = P.object_id
84
85 LEFT OUTER JOIN sys.identity_columns K
86 ON P.object_id = K.object_id
87
88 WHERE i.index_id < 2 -- GET ONLY THE CLUSTERED INDEXES - IF EXISTS ANY
89 --and is_ms_shipped=0
90 AND k.is_replicated=1
91 ORDER BY [Table]
92 --***************************************--
93OPEN replicatedTableCur
94FETCH NEXT FROM replicatedTableCur INTO @replicatedTableCur
95 WHILE @@FETCH_STATUS = 0
96 BEGIN
97 --replicatedTableCur--\
98 DECLARE usersCur CURSOR FOR
99 SELECT * FROM #usersToReplicate
100 OPEN usersCur
101 FETCH NEXT FROM usersCur INTO @usersCur
102 WHILE @@FETCH_STATUS = 0
103 BEGIN
104 --usersCur--\
105 SET @sqlcommand = 'DENY INSERT, UPDATE, DELETE on [dbo].['+@replicatedTableCur+'] TO ['+@usersCur+']'
106 IF @repliName IS NULL OR EXISTS (select bo from #repliObject where bo = '[dbo].['+@replicatedTableCur+']')
107 BEGIN
108 print @sqlcommand
109 --EXECUTE sp_executesql @sqlcommand
110 END
111 ------------/
112 FETCH NEXT FROM usersCur INTO @usersCur
113 END
114 CLOSE usersCur
115 DEALLOCATE usersCur
116 ----------------------/
117FETCH NEXT FROM replicatedTableCur INTO @replicatedTableCur
118END
119 CLOSE replicatedTableCur
120 DEALLOCATE replicatedTableCur
121
122END