Monday, October 03, 2011
Thursday, September 22, 2011
Build Hierarchy table from core table
if(object_id('tempdb..#tmp')>1)
drop table #tmp
declare @depth int = 0 , @count int = 0
create table #tmp (parent_id int, child_id int, depth_from_parent int)
SET @depth = 0;
TRUNCATE TABLE #tmp;
INSERT INTO #tmp(parent_id, child_id, depth_from_parent)
VALUES (1, 1, @depth)
WHILE @@ROWCOUNT > 0
BEGIN
SET @depth = @depth + 1
INSERT INTO #tmp(parent_id, child_id, depth_from_parent)
OUTPUT inserted.child_id, inserted.child_id, 0 INTO #tmp
SELECT 1, ou.ou_id, os.depth_from_parent + 1
FROM #tmp os (NOLOCK)
INNER JOIN ou (NOLOCK) ou ON os.child_id = ou.parent_id
WHERE os.depth_from_parent = @depth - 1;
END
SET @depth = 0
WHILE @@ROWCOUNT > 0
BEGIN
SET @depth = @depth + 1;
INSERT INTO #tmp(parent_id, child_id, depth_from_parent)
SELECT os.parent_id, ou.ou_id, @depth
FROM #tmp os
INNER JOIN ou (NOLOCK) ON os.child_id = ou.parent_id --AND os.parent_id <> ou.ou_id -- exclude circular reference
WHERE os.depth_from_parent = @depth - 1 AND os.parent_id <> 1
END
Monday, September 19, 2011
Monday, September 12, 2011
Friday, September 02, 2011
Friday, August 26, 2011
Monday, August 22, 2011
Wednesday, August 03, 2011
minimum account permissions for service broker
USE USERDB
go
CREATE SCHEMA [office\user] AUTHORIZATION [office\user];
GO
CREATE USER [office\user] WITH DEFAULT_SCHEMA = [office\user];
GRANT CREATE PROCEDURE TO [office\user];
GRANT CREATE QUEUE TO [office\user];
GRANT CREATE SERVICE TO [office\user];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [office\user];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [office\user];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [office\user];
GRANT CONTROL ON SCHEMA::[dbo] TO [office\user];
GRANT IMPERSONATE ON USER::DBO TO [office\user];
GO
go
CREATE SCHEMA [office\user] AUTHORIZATION [office\user];
GO
CREATE USER [office\user] WITH DEFAULT_SCHEMA = [office\user];
GRANT CREATE PROCEDURE TO [office\user];
GRANT CREATE QUEUE TO [office\user];
GRANT CREATE SERVICE TO [office\user];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [office\user];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [office\user];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [office\user];
GRANT CONTROL ON SCHEMA::[dbo] TO [office\user];
GRANT IMPERSONATE ON USER::DBO TO [office\user];
GO
Subscribe to:
Posts (Atom)