OK, I know about this: [URL] ....
But the script has "ALL SERVER".
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".
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?
View 0 Replies View RelatedAre there ways in SQL server to deny connecting to a instance? Basically i am looking for a way where data is accessed only through application , even the DBA's shouldn't have access to the data though they are sysadmins? Someone told me this is possible in SQL 2014? This is only for individual access, i know i can put some encryption in app layer.
View 9 Replies View RelatedI 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),
[code]....
I have a temptable with a list of user IDs that I want to drop so I created a script to do a cursor and run through my drop functions. The drops work by themselves and the ver check works with them but when I wrap them in the cursor all i get is an output for each user in the results window in ssms. why it's not setting the variable and instead outputting to results?
DECLARE @ver nvarchar(128);
DECLARE @UserName nvarchar(50);
DECLARE @UserD nvarchar(80);
DECLARE @LoginD nvarchar(80);
-- Initialize the variable.
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
[code]...
SQL Server 2005 SP2 add LOGON TRIGGER feature,
but, if logon trigger have bug, it seems that anyone can connect to the sql server.
for example:
create trigger tr_logon1_bug
to all server
for logon
as
insert into t1 values(original_name(), getdate());
and, drop table 't1'.
the result, everyone fail to logon, even if sa. (may be..)
therefore, cannot disable/drop this trigger ...
So, Are there anyway to disable logon trigger,
or to connect by sa/Admin,
when logon trigger failed.
Regards,
Is it possible to ONLY allow a login to the availability group listener, but block logins to server instance/nodes?
So: MySQLServerA and MySQLServerB are in an avail group "MyAvailGroup".
I want users to login to MyAvailGroup's listener, but I do NOT want them to login to the actual hosts/nodes directly.
Is that possible?
Hi all,
I'm writing a trigger to prevent duplicates. I know that this can be done through primary key or unique constraints but in the real world my uniqueness is defined by 8 columns which is too a big an index to maintain on the primary / unique key.
If I create a table with 2 columns
CREATE TABLE Table1
(CentreCHAR(10),
Month CHAR(3)
)
Then create a trigger to prevent duplicates
CREATE TRIGGER trigger_Check_Duplicates
ON Table1
FOR INSERT, UPDATE
AS
-- This trigger has been created to check that duplicate rows are not inserted into AudioVisual table.
DECLARE @IsDuplicate INTEGER
-- Check if row exists
SELECT @IsDuplicate = 1
FROM Inserted i, Table1 t
WHERE t.Centre = i.Centre
AND t.Month = i.Month
IF (@IsDuplicate = 1)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('This row already exists in the table', 16, 1)
ROLLBACK TRANSACTION
END
Then insert a row into the new table (no other data is in there)
INSERT Table1
VALUES('0691040176','AUG')
I get the Trigger error message that the row already exists. Why is this the case? I though that Table 1 (target table) would show no entries as it has no data - it should be a before image of the table and the inserted table should be an after image.
Please help!!!
Thanks
Neill
Hi,
I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.
Any Ideas..
Is it possible to create a trigger to prevent update of a row if no signifiant colums are updated
Example : I need to allow the update only if colums a,b or c have changes ?
how it should works to keep or discard the update according to condition ?
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
[Code] ....
I have this table:
CREATE TABLE Workspaces (
AreaNr CHAR(2)
CONSTRAINT ck_a_areanr REFERENCES Areas(AreaNr)
ON DELETE CASCADE
[Code] ....
Now I want to create a trigger that prevents a delete on a single row (randomly chosen) from the table Workspaces. At the moment I have the following trigger, but this trigger still allows removal of single rows.
Current trigger:
CREATE TRIGGER deleteWorkspace ON Workspaces
FOR DELETE AS
BEGIN
DECLARE @Count int
SET @Count = @@ROWCOUNT;
[Code] ....
Desired result: I want to be able to prevent a delete on a single row on the table above.
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?
Does anyone have a good strategy or technique for preventing database drops in SQL Server 2000? I know in 2005 DDL triggers rock, but in 2000 what can you do to audit who drops a database why keeping the same permissions intact.
Jason
When i process a order in table Orders_t i would like to check in storage_t if we defenetly have it in storage. .... if we have it in storage, i decrease the "amount" by 1 ..(amount -1), and process the order. Otherwise it will return nothing.
This is what i´ve come up with so far:
CREATE TRIGGER checkInStorage
ON orders_t
FOR INSERT, UPDATE
AS
DECLARE
@tOrderId char(3),
BEGIN
SET @tOrderId = (SELECT orderId FROM INSERTED)
--check if the amount in storage
IF EXIST(SELECT amount FROM storage_t WHERE orderId = @tOrderId and amount >= 0)
BEGIN --if it return true, i update the storage by decrease the amount with one
UPDATE storage_t
SET (amount = amount - 1)
WHERE orderId = @tOrderId
END
this doesn´t work...
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
and suggestions as to why and how to fix it???
Thanks in advance
Hi all,
I tried to use dbo.tables of Northwind database in SQL Server Express to do SqlCommand to populate a DataTable in DataSet. The SqlCommand failed in the project execution and I found that Northwind database in SQL Server Express is busted and gone (just the name "Northwind" remained in the SQL Server Management Studio Express). How can I prevent it from happening in my "SqlCommand-SqlConnection" project? Please help and advise.
I tried to repair my "Northwind" database by using the SQL2000SampleDb.msi of Northwind and pubs Sample Databases for SQL Server 2000 downloaded from the http://www.microsoft.com/downloads. My "pubs" database is still in my SQL Server Management Studio Express. How can I just repair my "Northwind" database by using the Microsoft SQL2000SampleDb.msi program? Please help and advise this matter too.
Thanks in advance,
Scott Chang
I manage a hosted environment - several actually. Meaning my company is the host. We provide some of our customers with a dedicated instance of SQL Server that they can use in read-only mode. Unfortunately, I have some very inquisitive customers who probably should be hosting their own SQL. They like to poke into the inner workings of our environment in ways that make me uncomfortable. What I originally found was that they were reading our server registry. I've disabled that. The next thing that bothers me is the amount of information available to them through SERVERPROPERTY. They don't need to know things like physical node of a cluster their instance is on, or the last time we updated SQL, or our licensing choices.
Is there any way to prevent a user from using the SERVERPROPERTY function? I've done a lot of digging and can't seem to find a way to do it.
In my database some of the store procedures getting the data from xml nodes.so I need to implement the validation to xml data for prevent sql injection.
View 0 Replies View RelatedI have 2 dbs (SQL 2012) - one contains a trigger that is enabled/disabled by a procedure in the other database. This all works fine.
If I create a Database Project solution in Visual Studio 2012 SSDT (or 2013) for both databases, the stored procedure generates a SQL71502 stating that my trigger name can't be resolved.
To recreate the issue:
CREATE DATABASE DbWithTrigger
GO
USE DbWithTrigger
GO
CREATE TABLE dbo.TblWithTrigger(
Id int NULL,
SomeValue varchar(30) NULL
[code]....
-- Test to confirm
EXEC CrossDbTriggerCall
INSERT DbWithTrigger.dbo.TblWithTrigger VALUES(1, 'Blah blah')
In Visual Studio 2012:
1. Create a new solution with a project named DbWithTrigger
2. In project settings set the Target platform to SQL 2012
2. Import the DbWithTrigger db into this project
3. Create a new project named DbCallsTrigger
4. In project settings set the Target platform to SQL 2012
5. Import the DbCallsTrigger db into this project
6. Add a Database Reference in DbCallsTrigger for DbWithTrigger
When you build the solution both dbs build successfully, however there are two warnings. One is easily resolved by replacing DbWithTrigger in the body of the procedure with [$(DbWithTrigger)] (db variable name for the reference) but I can't find out how to get rid of the other. Is it a bug?
THe Scenario is We have Tables like parent and Child Table.
Like we have Child Table as Name AcademyContacts,In that we have Columns like
Guid(PK)Not Null,
AcademyId(FK), Not Null,
Name,Null
WorkPhone,Null
CellPhone,Null
Email Id,Null
Other.Null
Since we have given Null to ''Workphone'',''Cellphone '', ''Email ID''.when inserting the data into these table if the particular columns are empty while inserting also the data will get populate into the table.And I need is if these columns are ''Workphone'',''Cellphone'' , ''Email ID'' they cant insert the data into table.Like it must trigger like ''Please enter atleast one of these ''Workphone'',''Cellphone'' , ''Email ID'' columns.
I have a report server that I restore from a copy of a backup from another SQL server on a daily basis. The problem is that the SQL logon in the report server does not work with the copy and I need to delete the logon in the database and reset the permissions for the SQL logon in order for the reports to run.
1. How do I enable the SQL logon to work automatically with the daily copy?
Thanks.
Dan
I have a trigger that executes AFTER INSERT, UPDATE, DELETE. Is there a way to disable and then reenable only the "AFTER DELETE", letting AFTER INSERT, UPDATE act normally?
View 6 Replies View RelatedWe have an UPDATE trigger that is failing. This seems like a basic task - we want to write a record to a separate tracking table when our main transaction table is updated for any reason. Our assumption is that we have a reference to the data from the "inserted" record that was just updated. The scenario here is that we are running a batch process which READS several thousand records in our transaction table each evening.
We then mark each individual record as processed on the transaction table and expect that the UPDATE trigger will successfully fire (it is not). The version of our trigger listed below shows our attempt to deal with the fact that TransactionID does NOT exist from "inserted." We also have a version of this trigger that deals with INSERTS - it works flawlessly.
ON [dbo].[FPS_Transaction]
AFTER UPDATE
AS declare @trxId uniqueidentifier;
BEGIN TRY
SET NOCOUNT ON
[code]...
I am STIGing a database and have to create a trigger that will stop any connection from occurring after a certain limit has been reached. For example I set the concurrent connection to 50 so I need a trigger that will stop any connection from all logins happening after 50.
The STIG info:
Rule Title: SQL Server must limit the number of concurrent sessions for each system account to an organization-defined number of sessions.
STIG ID: SQL2-00-000100
The below triggers works, but for only the one login, so how can I have it include all logins for that SQL instance.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
I have one old sql 2000 server and I backup the database and restore it to SQL 2008 R2 database. I can see all the tables and users but I do not know, how to move database user and logon's.
View 3 Replies View RelatedHi All,
My Report Server works find but after apply SQL Server 2005 Service Pack2. I can't connect to ReportServer Database. When Trying to connect from SQL Server Management Studio i get following Page.
TITLE: Connect to Server
------------------------------
Cannot connect to VISTA.
------------------------------
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.3054.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLORFF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; colorFF3300; TEXT-DECORATION:underline}
</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>The report server cannot open a connection to the report server database. The logon failed. (rsReportServerDatabaseLogonFailed) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsReportServerDatabaseLogonFailed&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.3054.00" target="_blank">Get Online Help</a></li><ul>
<li>For more information about this error navigate to the report server on the local server machine, or enable remote errors</li>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</ul>
</body>
--. (Microsoft.SqlServer.Management.UI.RSClient)
------------------------------
BUTTONS:
OK
------------------------------
Colud anyone help me to resolve this issue please???
Thank you very much.
Regards,
Balwant Patel
I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.
View 9 Replies View RelatedI am writing a trigger for getting values to auditlog table when the values gets updated. Below is the code of my trigger.
CREATE TRIGGER [dbo].[Update_Temp] ON [dbo].[Temptable1] FOR UPDATE
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
[Code] ....
The code is working fine when the table has primary key associated. However due to some restrictions I will not be able to have a primary key for some tables. I want to implement the same trigger in those tables too. When there is primary key, that primary key needs to get inserted into the audit table and if there is no primary key, i want a specific column value to get inserted instead of the primary key value into the audit table.
For example, i have a student table in which there is a student id, name, dob. there is no primary key defined for the table. So when i update the name or dob, i need the student id to get inserted into the Pk column of the audit table.
I tried modifying the code by checking the @pkcols for Null and if its null to get the old value as the primary key however I was not able to do it .
Hello,
I have set up 3 servers - Primary, Mirror and Witness. When I run the database mirroring wizard all my endpoints are configured, but when i start the mirroring service i get a 1418 error - in the logs in says -
Database Mirroring login attempt by user 'NT AUTHORITYANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITYANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.20.1.5]
I have looked through the BOL and can't find anything helpful. - I'm pretty new to SQL server, so any help is very much appreciated!
Thanks
Kerpoise
I create a Trigger that allows to create news row on other table.
ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX]
ON [dbo].[_IMPORT_FILES_CONTACTS]
AFTER INSERT
AS
[code]...
But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :
C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC001 - ACCOUNT 001
C003 - AD003 - AC001 - ACCOUNT 001
C004 - AD004 - AC001 - ACCOUNT 001
C005 - AD005 - AC001 - ACCOUNT 001
I search a means to have 50 lines differently on my CONTACT table.
C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC002 - ACCOUNT 002
C003 - AD003 - AC003 - ACCOUNT 003
C004 - AD004 - AC004 - ACCOUNT 004
C005 - AD005 - AC005 - ACCOUNT 005
I am inserting a record in XYZ table(DB1). Through trigger it will update ABC table(DB2).
I am getting error when doing above thing. What are the roles to be set to user to avoid above problem.
I have a problem described as follows: I have a table with one instead of insert trigger:
create table TMessage (ID int identity(1,1), dscp varchar(50))
GO
Alter trigger tr_tmessage on tmessage
instead of insert
as
--Set NoCount On
insert into tmessage
[code]....
When I execute P1 it returns 0 for Id field of @T1.
How can I get the Identity in this case?
PS: I can not use Ident_Current or @@identity as the table insertion hit is very high and can be done concurrently.Also there are some more insertion into different tables in the trigger code, so can not use @@identity either.