Prev: TEMPDB full
Next: Error 539, Schema changed after the target table was created
From: mpaine on 14 Dec 2006 15:47
I am having difficulties dropping a user from a database because of
this error:

Cannot drop schema 'usr' because it is being referenced by object
'SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de'.

I guess this could be that I had Query Notifications still open.

select * from sys.objects where
name='SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de'


yields:

SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de 203199824 NULL 5 0 SQ SERVICE_QUEUE 2006-12-13
01:08:47.420 2006-12-13 01:08:47.420 0 0 0

I don't know how to fix this but this is how I both create and remove
the user:

ALTER USER [usr] WITH DEFAULT_SCHEMA = [usr]
GO

CREATE SCHEMA [ja_usr] AUTHORIZATION [ja_usr]
GO

EXEC sp_addrole 'sql_dependency_subscriber'
EXEC sp_addrole 'sql_dependency_starter'

-- Permissions needed for [sql_dependency_starter]
GRANT CREATE PROCEDURE to [sql_dependency_starter]
GRANT CREATE QUEUE to [sql_dependency_starter]
GRANT CREATE SERVICE to [sql_dependency_starter]
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
to [sql_dependency_starter]
GRANT VIEW DEFINITION TO [sql_dependency_starter]

-- Permissions needed for [sql_dependency_subscriber]
GRANT SELECT to [sql_dependency_subscriber]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO
[sql_dependency_subscriber]
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
to [sql_dependency_subscriber]

-- Making sure that ja_webusr is member of the correct roles.
EXEC sp_addrolemember 'sql_dependency_starter', 'usr'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'usr'

GO




EXEC sp_droprolemember 'sql_dependency_starter', 'usr'
EXEC sp_droprolemember 'sql_dependency_subscriber', 'usr'

REVOKE CREATE PROCEDURE to [sql_dependency_starter]
REVOKE CREATE QUEUE to [sql_dependency_starter]
REVOKE CREATE SERVICE to [sql_dependency_starter]
REVOKE REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
to [sql_dependency_starter]
REVOKE VIEW DEFINITION TO [sql_dependency_starter]

-- Permissions needed for [sql_dependency_subscriber]
REVOKE SELECT to [sql_dependency_subscriber]
REVOKE SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]
REVOKE RECEIVE ON QueryNotificationErrorsQueue TO
[sql_dependency_subscriber]
REVOKE REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
to [sql_dependency_subscriber]

EXEC sp_droprole 'sql_dependency_subscriber'
EXEC sp_droprole 'sql_dependency_starter'

DROP SCHEMA [usr]
GO

From: mpaine on 14 Dec 2006 17:46
I tried the following to clean up the Notification conversations, while
helping some, didn't solve my problem:

DECLARE @ObjName nvarchar(max)
DECLARE @Msg nvarchar(max)
select @ObjName = name from sys.objects where name like
'SqlQueryNotificationService%'
IF (@ObjName like 'SqlQueryNotificationService-%')
BEGIN
DECLARE @ConvHandle uniqueidentifier
DECLARE Conv CURSOR FOR

SELECT conversation_handle
FROM sys.conversation_endpoints
WITH (nolock)
WHERE state = 'DI' and far_service = @ObjName;

OPEN Conv;
FETCH NEXT FROM Conv INTO @ConvHandle;
WHILE (@@FETCH_STATUS = 0) BEGIN
END CONVERSATION @ConvHandle WITH CLEANUP;
FETCH NEXT FROM Conv INTO @ConvHandle;
END
CLOSE Conv;
DEALLOCATE Conv;
END




mpaine(a)htxml.com wrote:
> I am having difficulties dropping a user from a database because of
> this error:
>
> Cannot drop schema 'usr' because it is being referenced by object
> 'SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de'.
>
> I guess this could be that I had Query Notifications still open.
>
> select * from sys.objects where
> name='SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de'
>
>
> yields:
>
> SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de 203199824 NULL 5 0 SQ SERVICE_QUEUE 2006-12-13
> 01:08:47.420 2006-12-13 01:08:47.420 0 0 0
>
> I don't know how to fix this but this is how I both create and remove
> the user:
>
> ALTER USER [usr] WITH DEFAULT_SCHEMA = [usr]
> GO
>
> CREATE SCHEMA [ja_usr] AUTHORIZATION [ja_usr]
> GO
>
> EXEC sp_addrole 'sql_dependency_subscriber'
> EXEC sp_addrole 'sql_dependency_starter'
>
> -- Permissions needed for [sql_dependency_starter]
> GRANT CREATE PROCEDURE to [sql_dependency_starter]
> GRANT CREATE QUEUE to [sql_dependency_starter]
> GRANT CREATE SERVICE to [sql_dependency_starter]
> GRANT REFERENCES on
> CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
> to [sql_dependency_starter]
> GRANT VIEW DEFINITION TO [sql_dependency_starter]
>
> -- Permissions needed for [sql_dependency_subscriber]
> GRANT SELECT to [sql_dependency_subscriber]
> GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]
> GRANT RECEIVE ON QueryNotificationErrorsQueue TO
> [sql_dependency_subscriber]
> GRANT REFERENCES on
> CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
> to [sql_dependency_subscriber]
>
> -- Making sure that ja_webusr is member of the correct roles.
> EXEC sp_addrolemember 'sql_dependency_starter', 'usr'
> EXEC sp_addrolemember 'sql_dependency_subscriber', 'usr'
>
> GO
>
>
>
>
> EXEC sp_droprolemember 'sql_dependency_starter', 'usr'
> EXEC sp_droprolemember 'sql_dependency_subscriber', 'usr'
>
> REVOKE CREATE PROCEDURE to [sql_dependency_starter]
> REVOKE CREATE QUEUE to [sql_dependency_starter]
> REVOKE CREATE SERVICE to [sql_dependency_starter]
> REVOKE REFERENCES on
> CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
> to [sql_dependency_starter]
> REVOKE VIEW DEFINITION TO [sql_dependency_starter]
>
> -- Permissions needed for [sql_dependency_subscriber]
> REVOKE SELECT to [sql_dependency_subscriber]
> REVOKE SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]
> REVOKE RECEIVE ON QueryNotificationErrorsQueue TO
> [sql_dependency_subscriber]
> REVOKE REFERENCES on
> CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
> to [sql_dependency_subscriber]
>
> EXEC sp_droprole 'sql_dependency_subscriber'
> EXEC sp_droprole 'sql_dependency_starter'
>
> DROP SCHEMA [usr]
> GO

From: mpaine on 14 Dec 2006 17:53

KILL QUERY NOTIFICATION SUBSCRIPTION ALL

didn't help either

mpaine(a)htxml.com wrote:
> I tried the following to clean up the Notification conversations, while
> helping some, didn't solve my problem:
>
> DECLARE @ObjName nvarchar(max)
> DECLARE @Msg nvarchar(max)
> select @ObjName = name from sys.objects where name like
> 'SqlQueryNotificationService%'
> IF (@ObjName like 'SqlQueryNotificationService-%')
> BEGIN
> DECLARE @ConvHandle uniqueidentifier
> DECLARE Conv CURSOR FOR
>
> SELECT conversation_handle
> FROM sys.conversation_endpoints
> WITH (nolock)
> WHERE state = 'DI' and far_service = @ObjName;
>
> OPEN Conv;
> FETCH NEXT FROM Conv INTO @ConvHandle;
> WHILE (@@FETCH_STATUS = 0) BEGIN
> END CONVERSATION @ConvHandle WITH CLEANUP;
> FETCH NEXT FROM Conv INTO @ConvHandle;
> END
> CLOSE Conv;
> DEALLOCATE Conv;
> END
>
>
>
>
> mpaine(a)htxml.com wrote:
> > I am having difficulties dropping a user from a database because of
> > this error:
> >
> > Cannot drop schema 'usr' because it is being referenced by object
> > 'SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de'.
> >
> > I guess this could be that I had Query Notifications still open.
> >
> > select * from sys.objects where
> > name='SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de'
> >
> >
> > yields:
> >
> > SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de 203199824 NULL 5 0 SQ SERVICE_QUEUE 2006-12-13
> > 01:08:47.420 2006-12-13 01:08:47.420 0 0 0
> >
> > I don't know how to fix this but this is how I both create and remove
> > the user:
> >
> > ALTER USER [usr] WITH DEFAULT_SCHEMA = [usr]
> > GO
> >
> > CREATE SCHEMA [ja_usr] AUTHORIZATION [ja_usr]
> > GO
> >
> > EXEC sp_addrole 'sql_dependency_subscriber'
> > EXEC sp_addrole 'sql_dependency_starter'
> >
> > -- Permissions needed for [sql_dependency_starter]
> > GRANT CREATE PROCEDURE to [sql_dependency_starter]
> > GRANT CREATE QUEUE to [sql_dependency_starter]
> > GRANT CREATE SERVICE to [sql_dependency_starter]
> > GRANT REFERENCES on
> > CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
> > to [sql_dependency_starter]
> > GRANT VIEW DEFINITION TO [sql_dependency_starter]
> >
> > -- Permissions needed for [sql_dependency_subscriber]
> > GRANT SELECT to [sql_dependency_subscriber]
> > GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]
> > GRANT RECEIVE ON QueryNotificationErrorsQueue TO
> > [sql_dependency_subscriber]
> > GRANT REFERENCES on
> > CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
> > to [sql_dependency_subscriber]
> >
> > -- Making sure that ja_webusr is member of the correct roles.
> > EXEC sp_addrolemember 'sql_dependency_starter', 'usr'
> > EXEC sp_addrolemember 'sql_dependency_subscriber', 'usr'
> >
> > GO
> >
> >
> >
> >
> > EXEC sp_droprolemember 'sql_dependency_starter', 'usr'
> > EXEC sp_droprolemember 'sql_dependency_subscriber', 'usr'
> >
> > REVOKE CREATE PROCEDURE to [sql_dependency_starter]
> > REVOKE CREATE QUEUE to [sql_dependency_starter]
> > REVOKE CREATE SERVICE to [sql_dependency_starter]
> > REVOKE REFERENCES on
> > CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
> > to [sql_dependency_starter]
> > REVOKE VIEW DEFINITION TO [sql_dependency_starter]
> >
> > -- Permissions needed for [sql_dependency_subscriber]
> > REVOKE SELECT to [sql_dependency_subscriber]
> > REVOKE SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]
> > REVOKE RECEIVE ON QueryNotificationErrorsQueue TO
> > [sql_dependency_subscriber]
> > REVOKE REFERENCES on
> > CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
> > to [sql_dependency_subscriber]
> >
> > EXEC sp_droprole 'sql_dependency_subscriber'
> > EXEC sp_droprole 'sql_dependency_starter'
> >
> > DROP SCHEMA [usr]
> > GO

From: Erland Sommarskog on 15 Dec 2006 05:52
(mpaine(a)htxml.com) writes:
> I am having difficulties dropping a user from a database because of
> this error:
>
> Cannot drop schema 'usr' because it is being referenced by object
> 'SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de'.
>
> I guess this could be that I had Query Notifications still open.

What if you perform an update to trigger a notification?

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: mpaine on 15 Dec 2006 18:26
The triggers should be expired at this point, so I don't think that
would help. I will try.


Erland Sommarskog wrote:
> (mpaine(a)htxml.com) writes:
> > I am having difficulties dropping a user from a database because of
> > this error:
> >
> > Cannot drop schema 'usr' because it is being referenced by object
> > 'SqlQueryNotificationService-59d3e8da-c223-425e-afe7-480d074356de'.
> >
> > I guess this could be that I had Query Notifications still open.
>
> What if you perform an update to trigger a notification?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: TEMPDB full
Next: Error 539, Schema changed after the target table was created