sqlDependency Problem

Question

I deleted the account owning Test Database after creating Test Database.

Everythings work well except SqlDependency.

SqlDependency OnChange Event is not working.

I have tested SQL 2005 ent RTM & VS 2005 Pro RTM.

I'm curious why it happened.

Thank you.

 

Answer 1

Hi Pom,

SqlDependency needs the EXECUTE AS infrastructure to do it's work. If the account that created the database was deleted, EXECUTE AS user 'dbo' will fail, because it cannot retrieve the account info. Change the database ownership to a valid account:

ALTER AUTHORIZATION ON DATABASE::[Test Database] TO [sa];

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

An mention by Iric, change the DB owner ship & try again :-

You can change the db owner :-

1) From SSMS GUI window

2) By Below query - old way

USE <DBNAME>
GO
EXEC dbo.sp_changedbowner @loginame = N'User Name'
GO

3) By query mention by Iric - new way

ALTER AUTHORIZATION ON DATABASE::[Test Database] TO [sa];

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.