Unable To Drop Login Due To Existing Event Notification
Aug 20, 2007
Hi
I am unable to drop the above login. The error is Error:15141 The server principal owns an event notification and cannot be dropped.
I have looked in the table sys.server_event_notifications and there are rows returned that have a server_principal_id of the user I am trying to drop. No events have been created on this server, so I am assuming these notifications are either created by default or are somehow related to Database Mail?
All the event notifications belong to service name "SQL/Notifications/ProcessWMIEventProviderNotification/v1.0" and begin with SQLWEP (i.e. SQLWEP_RECHECK_SUBSCRIPTIONS, SQLWEP_B415ADB8_A604_4057_976F_600002FA5AF6). How can I find out what these are for and how they were created?
What purpose do these event notifications have? Is there some syntax to change the owner of these event notifications so I can successfully drop the login? If the only way is to directly update the system view, is this safe and what repercussions could this have?
Does anyone know if there is any software available that notifies specified people when an error above a certian level occurs. Im thinking along the lines of email and text message.
Im running sql server 2k at sp3a level, and the software will have to be compatible with it so as to get the event notification to pass the information on.
If there isn't any software, would anyone know of any scripts that will do this?
I am trying to set up the email notification system in SQL server 2000, but im having some problems. Mainly, im having problems with setting up an email account in the first place (there is a dedicated mail box, but the one im working with is a different box altogether).
If anyone could help fathom this out from start to finish i would be really grateful.
I have a simple question regarding event notification in sql 2005. I keep up getting a message indicating a syntax error when trying to execute the following script. I also tried with [ instead of '.
CREATE EVENT NOTIFICATION NotifyCT ON DATABASE FOR CREATE_TABLE TO SERVICE 'NotifyService'
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'NotifyService'.
I have implemented Event Notification sample you provided in my logic, but I am perplexed that even though my watched queue does go down, I do not get message in the Event_Collector queue all the time. The Event_Collector queue does stay enabled though. Any thoughts? I guess any suggestions on debugging the event notification portion would be helpful.
I would like to propagate an event signal to an external applicationwhen a table in my MSSql2000 server is updated.Prog A; I have an external application adding records to a table.Prog B; I have another external application using this table as well.When Prog A creates a new record in the Table, how can I have Prog B benotified of the event without polling the table or creating a linkbetween Prog A and Prog B.Thanks.
If I call ExecuteNonQuery() in a timer event callback in a console application, it hangs. Why is that?
.B ekiM
class Program { static SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NONEOFYOURBISUINESS;Data Source=localhost");
static void Main(string[] args) { NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX(); NativeMethods.GlobalMemoryStatusEx(mem); Console.WriteLine("{0} bytes", mem.ullAvailPhys);
System.Timers.Timer aTimer = new System.Timers.Timer(); // Set the Interval to 2 seconds (2000 milliseconds). aTimer.Interval = 1000; aTimer.Enabled = true;
// Hook up the Elapsed event for the timer. aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);
Console.ReadLine(); }
private static void OnTimedEvent(object source, ElapsedEventArgs e) { NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX(); NativeMethods.GlobalMemoryStatusEx(mem);
SqlCommand cmd = new SqlCommand( "INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (1, @When, @AvailPhys);" + "INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (2, @When, @AvailPageFile);" + "INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (3, @When, @AvailVirtual);" + "INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (4, @When, @AvailExtendedVirtual);");
I have configured an Event Notification for BROKER_QUEUE_DISABLED.
I created a Queue different from the one the Receive my normal messages and on this queue I define an activation.
The Activated SP takes the [EventNotification] message and execute a
RAISEERROR('Message',0,1) WITH LOG.
The error is properly logged in the SQL server event log but I gave a look the Initiator and target sys.conversation_endpoints catalog view and I have see the the conversations are in the status CONVERSING or SI ...
Do I have to call the END CONVERSATION after the commit or these statuses are caused by some mistakes on the message process workflow?
Can anyone spot what i am missing here ? The problem is that i am getting a null object for e.TextData in the t_OnEvent(object sender, TraceEventArgs e) function below. I am trying to get event- notifications while processing the data mining structure.
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
I am facing this problem with SQL Server 2000 as well as with SQL Server 2005. I want to get notified when a deadlock occurs. This is what I have done.
I have setup an SQL Server Agent alert for the error number 1205. The alert is enabled and in response to the alert I am invoking a job and also tried email and net send etc.
Now I simulated deadlock situation by using two connections and received the 1205 error. However when I go back to the alert and see it does not invoke the job nor send message nor email. I find the number of occurrances of the alert as 0 which means that the alert never occurred. Why am I not able to trap the 1205 error message and get a notification?
I understand that I can enable trace flag 1204 and trace flag 3605 to get the deadlock event and the complete description logged to the sql server error log. But my question is why am I not able to use the 1205 event in the sql server agent alert. Even if I enable 1204 and 3605 I am only able to get the information of the deadlock in the error log but the alert is not getting triggered.
I am able to get all other alerts except the alert for 1205 and in general for any severity 13 alerts they are not getting triggered.
Any help is appreciated. I got this problem with sql server 2000 sp4 and sql server 2005 sp1.
I need to create a login which has the same access permissions on databases and its objects as an existing login.
I understand in SQL Server that a login is for the instance and the user is for the database. And for each login to access a database there must be an associated user within the database.
Example: login u1 has access to databases d1 and d2. And u1 has the dbowner privilege on d1 And u1 has the db_datareader privilege on d2
I want to create another login u2 which has identical privileges and access as u1.
I need this because in the case that I am working on, this u1 login is associated with almost 30 database within the instance.
hello!! i am using a couple of temp tables for a select statement. i need to drop the tables only if they exist before using them, because if i don't drop, then i will get this error:
There is already an object named '#Tmp01' in the database.
and if i try to drop the tables for the first time i run the query, then i will get an error saying that i cannot drop a table that doesn't exist.
i have tried using this sentence:
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '#Tmp01') DROP TABLE #Tmp01 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '#Tmp02') DROP TABLE #Tmp02 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '#Tmp03') DROP TABLE #Tmp03
but it only seems to work with normal tables, since temp tables are not found in sysobjects.
I receive this error message occasionally in my ETL - usually when trying to pull data from our source system:
An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in login response". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: Timeout error [258]. ". There is a setting in the connections manager, under the ALL tab, Initialization, Connect Timeout. It is set to zero, which would be the default. It appears (by looking at the timestamps in my error log) that the default is 15 seconds. If I change this to a higher value (60 seconds or so) should this fix the error?
Is there a way to set up automated email notification beginning 14 days prior to when a SQL Server Login password (that has "Enforce password expiration" enabled) will expire?
Let's say I have a function named MyCustomFunction and I want to ensure that it exists in the database. Let's say I have a create script for the function. I want the script to be runnable multiple times if needed. A common way to do this is to check for an object_id at the top of the function like this:
IF OBJECT_ID('MyCustomFunction') IS NULL DROP FUNCTION MyCustomFunction GO CREATE FUNCTION MyCustomFunction...
But is there a more elegant way to do this? For example, instead of dropping and recreating the function, is there a way to simply exit from the script and do nothing if the function already exists? Something like this:
IF OBJECT_ID('MyCustomFunction') IS NULL RETURN GO CREATE FUNCTION MyCustomFunction...
I have a table which I am unable to drop the index. I tried to drop it via the TSQL and GUI interface (Indexes dialog box), but when I clicked on table refresh, the index still there.
Below is the result after DBCC commands:
1. DBCC checktable(TB_LOCLoan)
Checking TB_LOCLoan The total number of data pages in this table is 146542. Table has 1596232 data rows. Msg 605, Level 21, State 1 Attempt to fetch logical page 462136 in database 'DM_LOCLoan' belongs to object '1744009244', not to object 'TB_LOCLoan'.
2. DBCC newalloc(DM_LOCLoan)
TABLE: TB_LOCLoan OBJID = 832005995 INDID=0 FIRST=60168 ROOT=642778 DPAGES=146542 SORT=0 Data level: 0. 146542 Data Pages in 18393 extents. INDID=5 FIRST=463752 ROOT=463739 DPAGES=5304 SORT=1 Msg 2525, Level 16, State 1 Table Corrupt: Object id wrong; tables: alloc page 463616 extent id=463752 l page#=463752 objid in ext=-832005995 (name = -832005995) objid in page=832005995 (name = TB_LOCLoan) objid in sysindexes=832005995 (name = TB_LOCLoan) TOTAL # of extents = 18393
3. From Error Msg 2525, I have tried the suggested action
use DM_LOCLoan go select indid, name, object_name(id) from sysindexes where id=832005995 and distribution=463752 go
This query does not return any row.
I am running out of clue of how to proceed! Anybody come across this problem before? I am very eager to solve this problem as the table is hanging here, I can't even drop it to recreate and continue data loading.
I restored a user database from another server and created a script to drop and recreate the users. This has worked for me in the past to synchronize logins/passwords. Recently I have been unable to drop two users and get the following error message:
Server: Msg 15183, Level 16, State 1, Procedure sp_MScheck_uid_owns_anything, Line 17 The user owns objects in the database and cannot be dropped.
I'm unable to find any objects owned by the user and have unsuccessfully been able to drop them. Has anyone else anything similar to this error?
According to sysindexes, I have a table with a primary key and an index. I'd like to drop the index. However, when I give the drop command, the message "Cannot drop the index 'eventlog._INDEX', because it does not exist in the system catalog." is returned.
I'm not sure what to look for. How do I remove the index?
I created a trigger that will throw a message whenever a new record is inserted in the table. Now I want to remove this trigger. I am not able to remove.
CREATE TRIGGER prod_culture_trig ON Production.Culture AFTER INSERT AS SELECT 'New culture entry added';
I get the following error message:
Msg 2714, Level 16, State 2, Procedure prod_oulture_trig, Line 4 There is already an object named 'prod_oulture_trig' in the database.
This error confirms that we have the trigger already existing. Now I run the code
DROP TRIGGER prod_oulture_trig;
I get the following error message - Msg 3701, Level 11, State 5, Line 1 Cannot drop the trigger 'prod_culture_trig', because it does not exist or you do not have permission.
What permission do I require? This is a test database on my computer with me as the administrator.
Hi all, I am trying to create a CONSTRAINT but for some reason T-SQL does not allow me to do that.
When I try to DROP a CONSTRAINT it says:
Msg 3728, Level 16, State 1, Line 13'DF_TBL_SyncTable_DEVUK' is not a constraint.Msg 3727, Level 16, State 0, Line 13Could not drop constraint. See previous errors.
When I try to ADD a CONSTRAINT it says: Msg 1781, Level 16, State 1, Line 14Column already has a DEFAULT bound to it.Msg 1750, Level 16, State 0, Line 14Could not create constraint. See previous errors.
For some reason I can't win here. Can't drop it nor can I create one. Any solution?
Code Snippet ALTER TABLE TBL_SyncTable DROP CONSTRAINT DF_TBL_SyncTable_DEVUK ALTER TABLE TBL_SyncTable ADD CONSTRAINT GOD_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK
Thanks for comments + suggestions.
*UPDATE*
I am trying to use the following code to check if the SCHEMA exists but still no luck. For some reason when I create it and wrap an IF statement around it, it doesn't detect the SCHEMA. Is something wrong with my code?
Code Snippet
IF EXISTS(
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'dbo'
AND CONSTRAINT_NAME = 'DF_TBL_SyncTable_DEVUK'
AND TABLE_NAME = 'TBL_SyncTable'
) SELECT * FROM TABLE_1 ELSE ALTER TABLE TBL_SyncTable ADD CONSTRAINT DF_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK
I set up a trigger to capture logins at the server level. This is part of some metrics I am putting together to properly set up load testing.
I am not surprised by the number of logins from the application logins, but what does surprise me is the number of logins from the various users that can query the production data.
As an example, my account has 1200+ logins for today only.
My first thought was that a process was set up to use my login by mistake. This also applies to some of the other users.But, there are no processes that are using any login except for the SQL service account or the applications logins.
I'm trying to drop logins from my server and anytime, I try to drop them. system gives me Msg 15175. Login XYZ is aliased or mapped to a user in one or more databases. Drop the users or alias first. I dont want to go to database and drop the user and then come back and drop the login. Why doesnt it do that in one short?
I've got a Windows login, which I cannot drop. I have checked about everything I can think of and can't find a connection to this user. Is there a script which anyone has which will list all object referencing this users? SQL 2005 ENT x64. It's a windows domain user.
I have created a Endpoint with "Mirroring Server" on the mirroring instance. Due to the keyword server i am not able to drop the endpoint now.
Also am not able to configure a mirror in a windows workgroup also( the two systems principal and mirror are under same network ). I have followed many ways as per msdn articles and other blogs.
I am not able to connect to the mirror server.
I have tried giving the ip addr and the port no, also the computer name:port no, computername.local:port no etc. Both the systems are running on sql server 2008 only
I always get the error as Connection cannot be established to the destination.
(I have also enabled the ports in firewall by creating inbound and outbound rules)
The issue probably lies in NAMING server address i guess.
I have a procedure where an id is passed into the procedure. It will either be a numeric value or it will be the word "ALL." All that is taking place is a select statement for the specified user id. However, if All is passed in then I 1st need to get a list of all possible user id's and produce the results for all of the associated user ids. This is my syntax, but it keeps producing a compile error of:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#tbl_temp', because it does not exist or you do not have permission.And I placed a comment above the line that is throwing this error.
