DB Engine :: Does Logon Trigger Create Internal Temporary Objects
Jun 4, 2015
I create a logon trigger on a sql server 2008 r2 instance,the trigger is very simple,like this:
CREATE TRIGGER tg_login
ON ALL SERVER
FOR LOGON
AS
IF ORIGINAL_LOGIN() IN('sa') AND HOST_NAME()='TestHost1'
[Code] ...
I use master.dbo.LoginRecord table to record the bad logon. When the trigger is working ,latches produced sometimes,wait type is PAGELATCH_UP and wait resource is 2:1:3. At this time, a large number of logon failed ,error message is "Logon failed for login 'login name' due to trigger execution."
I think logon trigger create internal temporary objects maybe,it is right?
I have a logon trigger on a SQL Server 2008 R2 Express Advanced production database to prevent remote logons. The trigger works fine. When I need to connect via my local machine, remotely, I connect via a VPN, can connect with SSMS and do whatever I need. However, if I use a linked server on my local machine (still connected via VPN), I receive the logon error
Msg 17892, Level 14, State 1, Line 1 Logon failed for login 'sa' due to trigger execution.
The trigger is below and it logs any failures, except for the linked server.
If I disable the trigger, the linked server connects ok.
CREATE TRIGGER [tr_MasterLogon] Â Â ONÂ ALL SERVERÂ WITH EXECUTE AS 'sa' Â Â FOR LOGON AS BEGIN Â DECLARE Â Â @ClientAddress varchar(48) = (SELECT client_net_address
Hi all, I got an error when i use crystal reports to open a report of a database in a web application. I use the server as Oracle 10g. The error is as follows.:
Logon failed. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
I have a database that has had a full text index at some point in it's history - but no longer does. The database is currently at SQL 2012 compatibility, but it was created on SQL2000. It still appears to have a row in the internal table sys.sysftinds - but some of the column values are NULL, and NULLs are not allowed. The database fails DBCC checks because of this.
Msg 8970, Level 16, State 1, Line 2 Row error: Object ID 25, index ID 1, partition ID xxxx, alloc unit ID xxxx (type In-row data), page ID (1:xxxx), row ID 0. Column 'sensitivity' was created NOT NULL, but is NULL in the row. DBCC results for 'sys.sysftinds'.
There are 1 rows in 1 pages for object "sys.sysftinds".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'sys.sysftinds' (object ID 25).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (xxxx.sys.sysftinds).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Running DBCC with repair_allow_data_loss has not worked.
Used linked-server during logon trigger? is it possible? i know we can access tables, SPs, etc with in that server, if i want to check something in other server during logon trigger, can i do it?
Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below
name   fileid   filename   filegroup   size   maxsize   growth   usage XX   1   I:DataMSSQL.1MSSQLDataNew XX.mdf   PRIMARY   68140032 KB   Unlimited   0 KB   data only XX_log   2   I:DataMSSQL.1MSSQLDataNew XX_log.LDF   NULL   1050112 KB   2147483648 KB   102400 KB   log only XX_2   3   I:DataMSSQL.1MSSQLDataNew XX_2.ndf   PRIMARY   15458304 KB   Unlimited   0 KB   data only XX_3   4   I:DataMSSQL.1MSSQLDataNew XX_3.ndf   PRIMARY   13186048 KB   Unlimited   0 KB   data only XX_4   5   I:DataMSSQL.1MSSQLDataNew XX_4.ndf   PRIMARY   19570688 KB   Unlimited   204800 KB   data only XX_5   6   I:DataMSSQL.1MSSQLDataNew XX_5.ndf   PRIMARY   19591168 KB   Unlimited   204800 KB   data only
2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.
What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its  other files.
What more data i need to ensure i submit an analysis to this.
What I want is a trigger that is specific to my DB called "JunkStuff". I only want to block a servername from connect to my super dooper DB "JunkStuff".
I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @Body NVARCHAR(2000),
The trigger makes use of the inserted and deleted psuedo tables.
When an event occurs where it fires the trigger, I am getting the following error:
"Msg 5243, Level 22, State 1, Procedure TR_PRICE_MAPPING_AUDIT, Line 39 An inconsistency was detected during an internal operation. Please contact technical support. Reference number 3."
I tried to drop the trigger and recreate it. Still the same issue.
I ahve other triggers that handle the same type of audit process i am doing, but do not cause an error.
Does anyone have any thoughts.
The Line 39, is the point where the OPEN call to the currsor.
Here is the code for the trigger:
Create TRIGGER [dbo].[TR_PRICE_MAPPING_AUDIT] ON [dbo].[REST_MENU_PRICE_INCREASE_MAPPING] AFTER UPDATE
DECLARE PRICE_CUR CURSOR FOR SELECT D.REST_ID, D.rest_price_mapping_id, I.PCT_INCREASE, D.PCT_INCREASE, I.IS_ACTIVE, D.IS_ACTIVE, I.IS_DELETE, D.IS_DELETE, D.BEGIN_DATETIME FROM INSERTED I, DELETED D WHERE D.rest_price_mapping_id=I.rest_price_mapping_id and d.pct_increase<>i.pct_increase AND D.BEGIN_DATETIME<=GETDATE()
OPEN PRICE_CUR FETCH NEXT FROM PRICE_CUR INTO @V_REST_ID, @V_REST_MAPPING_ID, @V_NEW_PCT_INCREASE , @V_OLD_PCT_INCREASE, @V_NEW_IS_ACTIVE, @V_OLD_IS_ACTIVE, @V_NEW_IS_DELETE, @V_OLD_IS_delete, @V_OLD_BEGIN_DATETIME
WHILE @@FETCH_STATUS=0 BEGIN BEGIN TRANSACTION --THERE IS A CHNAGE IN ONE THE KEY ELEMENTS OF THE TABLE, -- WE NEED TO SET CURRENT TO END_DATE AND INSERT NEW RECORD INSERT INTO REST_MENU_PRICE_INCREASE_MAPPING_AUDIT (REST_ID,REST_PRICE_MAPPING_ID,PCT_INCREASE, BEGIN_DATETIME,END_DATETIME, IS_ACTIVE,iS_DELETE) VALUES(@V_REST_ID,@V_REST_MAPPING_ID, @V_OLD_PCT_INCREASE, @V_OLD_BEGIN_DATETIME,GETDATE(), @V_OLD_IS_ACTIVE,@V_OLD_IS_DELETE)
UPDATE REST_MENU_PRICE_INCREASE_MAPPING SET BEGIN_DATETIME=GETDATE() WHERE REST_PRICE_MAPPING_ID=@V_REST_MAPPING_ID COMMIT TRANSACTION FETCH NEXT FROM PRICE_CUR INTO @V_REST_ID, @V_REST_MAPPING_ID, @V_NEW_PCT_INCREASE , @V_OLD_PCT_INCREASE, @V_NEW_IS_ACTIVE, @V_OLD_IS_ACTIVE, @V_NEW_IS_DELETE, @V_OLD_IS_delete, @V_OLD_BEGIN_DATETIME END CLOSE PRICE_CUR DEALLOCATE PRICE_CUR END
we run 2008 std edition (I believe r2 on this server, will double check) and I need to find a needle in a haystack.
I am looking at two tables and would like to learn which, if any objects on same db updates these tables.  I'd even live with any references to these tables. I already know sql agent is out of the picture. I'm looking for a static approach, not a monitoring one because its possible updates don't occur till month end.
Is there a way in sql server to query the answer to such a question using dmvs or something like dmvs?  What if I get bold and want to search all db's on this server for objects that update my two tables even across dbs?
In the mean time, i'll look at scripting all triggers and procs , and doing a search on these table names in the scripts.Â
I don't know that I've ever seen a way to script all triggers before. Procs I can easily script from the object explorer details. But I'm not sure if in explorer details--> server objects --> triggers does what I hope it does.
I have come across this error message in ssrs and cannot seem to locate the error. Report manager lets me logon-- I can navigate to the folder but once I click on the actual report I get this error
When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed. These stored procedures are deleted only when the connection is closed. My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.
My questions: Why it occur ? Wich have created this stored procedure ? How to avoid it occur ?
The versions are: SQL Server 6.5 Visual Basic 5.0 SQL Server ODBC Driver 2.65.0240
it seems like i am able to create it. But when i try to access that temporary table, i get an error "Invalid object". this is happening only when i try to create local temporary table. Global temporary table works fine inside the dynamic SQL.
Hi, I'd like to create a temporary table (# or ## table) and have it available in DataFlow to work with it. I see I can create it using "Execute SQL Task" component in ControlFlow; but it's not available in DataFlow. It seems it should be created by a DataFlow component to be seen in the same DataFlow. Any Idea on this?
ERD above is what my database looks like (ignore it's in Access, database is in SQL 2005)
I have this code:
SELECT orderID, orderAmount = SUM(customerID)/customerID FROM orders GROUP BY orderID -- When Sum of the customer ID (and then) divided by the customer ID > 1 HAVING orderAmount > 1
which doesn't work because I never did find out how you make a column in the results to output your maths in. orderAmount doesn't exist as a column in the database, but for this query it should show only those customers who have ordered more than once with the company.
Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(2)) as ShmCoy,cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))as char(10)) as ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.
I would like to know if there is any way to restrict users from creating temp tables.
Problem: I am facing problems with lots of temporary objects getting created in my database. The users have read-only access to the database for adhoc-querying purpose through QA. Yet they are able to create temporary tables in tempdb database taking lot of resources on tempdb disk causing abnormally high growth of tempdb.
I've been using replication for a long while now but have never come across this error. It's a basic transactional replication from ServerA to ServerB, where ServerA is also the distributor. Everything had been running fine on it until yesterday, when this error started popping up and no further transactions could be delivered.
After some quick googling I was able to determine that the distribution agent account needed write access to C:Program FilesMicrosoft SQL Server100Com. According to the MSFT article it's because the distribution agent is running under a non-default profile. I didn't change this. However, what I did change around the time that these errors started occurring was the server's Replication Max Test Size setting. It would be far too coincidental for this to not be the cause, but what I don't understand is *why* that would have changed it.
how do I change this? It is definitely not preferable to create temp files in this directory in our environment.
I will have a group of developers creating objects in a database. Currently they all create them with the SA account. I would like to have them mapped to the dbo or some method that when they create objects all objects are dbo.object name and not h1fed99.object name. Is there a good methor or practice to handle this
My website host allows me to create DB and DB users from its control panel and then I am able to use SQL server Management studio to Manage My DB. Therefore I create My DB (TBC20) and create a user for it(Tony) from Host Panel. When I want to Export My Local copy DB data to the remote DB using Import/Export wizard and set the It won’t Generate any DB objects. Even When I use aspnet_regsql Command I encounter Following error: SQL error number is 262 and the SqlException message is: CREATE DATABASE permiss ion denied in database 'master'. Creating the TBC20 database... ------------------------------------------------------ When I View permissions in Database properties window No Permission is Grant for user: Tony. Only some permission Grant for Grantor: dbo. When I check Boxes to Grant Permission for user Tony None of permissions will be grant to this user. How could I allow this user ti take ownership of DB and create DB objects
Hi, can we use sqlcmd.exe (successsor to osql) to run sql scripts against a compact edition database? Would like to use the output from Visual Studio Team Edition for Software Professionals to build the database structure on compact edition database.
Hi All, I have a SQL script which has creation of some table variables. When I run this query in Query Analyzer, I get proper results. But, when I use the same script to execute from VB Script, it is not running at all. I tried a simple select statement which works fine; but with the one I want to run, it fails to run. I am trying to print the SQL script from VB script, and it appears to be fine. I think the creation of DB objects is where it is getting stuck. Any help here please??
Below is the SQL Script.
Code Block DECLARE @COMPSINGRP TABLE (NAME VARCHAR(100)) INSERT INTO @COMPSINGRP SELECT NAME FROM COMPUTER WHERE IDCOMPUTER IN (SELECT INSTANCEID FROM [FN_COMPUTERSINGROUPLIKENAME] ('UBS: APPLICATIONS - DEALAXIS') ) DECLARE @COMPRESTARTS TABLE (NAME VARCHAR(100)) INSERT INTO @COMPRESTARTS SELECT COMPUTER FROM ( SELECT C.Name AS COMPUTER, --- FIRST COLUMN (SELECT AC.Description FROM Computer CC INNER JOIN [fn_ComputersInGroupLikeName] ('UBS: Applications - DealAxis') CCG ON CC.idComputer = CCG.InstanceID INNER JOIN Alert AC ON CCG.InstanceID = AC.idComputer WHERE (AC.Description Like '%down on a scheduled maintenance%') AND CC.Name = C.Name AND (CONVERT(NVARCHAR(12),A.TimeOfLastEvent,111) = CONVERT(NVARCHAR(12),GETDATE(),111) )) AS DOWN_DESCRIPTION, --- SECOND COLUMN (SELECT AC.Description FROM Computer CC INNER JOIN [fn_ComputersInGroupLikeName] ('UBS: Applications - DealAxis') CCG ON CC.idComputer = CCG.InstanceID INNER JOIN Alert AC ON CCG.InstanceID = AC.idComputer WHERE (AC.Description Like '%successfully up now after a scheduled reboot%' ) AND CC.Name = C.Name AND (CONVERT(NVARCHAR(12),A.TimeOfLastEvent,111) = CONVERT(NVARCHAR(12),GETDATE(),111) ) ) AS UP_DESCRIPTION --- THIRD COLUMN FROM Computer C INNER JOIN [fn_ComputersInGroupLikeName] ('UBS: Applications - DealAxis') CG ON C.idComputer = CG.InstanceID INNER JOIN Alert A ON CG.InstanceID = A.idComputer WHERE (CONVERT (NVARCHAR(12),A.TimeOfLastEvent,111) = CONVERT(NVARCHAR(12),GETDATE(),111) ) --ORDER BY C.NAME ) AS RESTARTS DECLARE @NOCLUE TABLE (NAME VARCHAR(100)) SELECT 'The Server '+ NAME+' did not have any restart information. Please Check.' FROM @COMPSINGRP WHERE NAME NOT IN (SELECT NAME FROM @COMPRESTARTS)
Any help or suggestion would help me out. Thanks a lot.
I tried to call a exe from sql server through trigger with xp_cmdshell, But its not working as expected, shows preemptive_os_pipeops in process under the activity monitor and the process got hang.
I've read already every thread in this forum regarding this topic with no results.
SQLServer and the corresponding services are at actual update level. The reporting services worked for 4 months without this problem. Suddenly, most of the time all reports return an internal error (it is like 70-30 for internal error) not depending which render format is used.
The system logs (eventlog, iis) show no errors. The ExecutionLog of RS show rsInternalError.
Looking in the log files in RS log directory, there is following stacktrace:
How to SQL server save information about create/drop objects action. How can I get this? Example for, a lot of objects(sp) in my database has been dropped, how I know who was dropped them (user login & time)?
I just upgraded our existing 6.5 installation with the new SQL server 7.0. I can't get any of the existing stored procedures which I imported from 6.5 into 7.0 to allow me to edit them. I do all of my design from Visual InterDev and the SQL Server 6.5 version would allow me to create and edit stored procedures. It would also allow me to create/design new tables.
With SQL Server 7.0 I do not have an option to edit or create any of these items. I have created a new login, assigned it a password, given it admin rights/roles and I am still unable to remotely create these items. What am I doing wrong?
I am looking to create a server wide trigger which will fire whenever a database gets created, I would like to know if there are any gotchas or anything that I need to look out for/test specifically.
The idea is to automatically grant some permissions and take some other actions whenever a database gets created.
I have a after update trigger that calculates one of the columns based on other column values. It has to be a trigger and only fires an update statement against the table itself when the column has changed. At a simple level it is doing something similar to the code below
IF UPDATE(Col1) update MyTable SET Col2 = Col1 WHERE Col2 <> Col1
It works everywhere except on one site where the trigger causes itself to recurse until it reaches the 32 level error. It can be fixed by checking whether there and any records in the inserted table at the top. Like so.
IF EXISTS (SELECT * FROM inserted) begin IF UPDATE(Col1) update MyTable SET Col2 = Col1 WHERE Col2 <> Col1 end
However, I would like to know whether there is some system setting other than "nested triggers"Â that I am missing that would cause the behaviour.
