MS SQL Server :: Disable Triggers (using Alter With Public)

Disable Triggers

Nov 21, 2001

I don't seem to be able to find a command to disable triggers. Can this
be done in SQL7?

Thanks.

Disable Triggers For A Single Process

Jun 21, 2007

I am writing an adapter for a two way integration between two databases. The table I am currently working on has an update and insert trigger to send data to the other database via service broker.

Asd I insert/update data via my adapter I don't want the triggers to fire but I do want them to fire if somebody else make data modifications while I am working with the table.

I am doing all mu modifications within a transaction but I found I couldn't disable the trigger within a stored procedure to keep the disable and enable as close together as possible.

Does anybody ahve any ideas how to overcome this scenario or am I worrying about nothing as my transaction will keep other users from modifying the data until I've finished.

Any advice would be great.

Cheers

View 4 Replies View Related

Deny Connect To Sql Permission Or Alter Login Disable

May 7, 2008

Hi All,

I would like to disable a user account from logging to the database. I would like to know the difference between deny connect to sql permission and disabling an account by alter login disable. Please advice. Thanks

View 1 Replies View Related

Alter All Objects In A Database / Views / Triggers

Apr 20, 2015

I am trying to replace object name in views , triggers, stored procs, UDF,TVF etc.I have created a automated script to replace 'dbo.Cust' with 'dbo.Customer' in all objects and generate script as ALTER Statements. some objects are still scripted out as Create. Reason is it has some extra space in between

CREATE TABLE #test1(
[NAME] [nvarchar](128) NOT NULL,
[DEFINITION] [nvarchar](max) NULL,
[DEFINITION_bk] [nvarchar](max) NULL,
[type] [char](2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

[code]....

View 5 Replies View Related

SQL Server 2008 :: Alter Table Triggers Recreate Table?

May 26, 2015

which ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?

View 2 Replies View Related

Alter Table Alter Column In MSACCESS. How Can I Do It For A Decimal Field?

Jul 23, 2005

Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Multiple Triggers On A Table Or Encapsulated Triggers

May 12, 2008

This isn��t an problem as such, it��s more of a debate.

If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn��t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.

www.handleysonline.com

View 12 Replies View Related

Alter Table Alter Column

Jul 20, 2005

I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO

View 2 Replies View Related

Alter Table Alter Column...

Oct 8, 2007

I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?

my code:
Alter table customers alter column age

View 7 Replies View Related

Public Role

May 13, 2002

I am new at this and we encountered a problem. Can names in the public role be deleted? We have some names that need to go - however the delete option does not high light?

I would appreciate your help..

View 8 Replies View Related

How To Deny DMV To Public?

May 14, 2008

Hi Guys,

I am unable to deny DMV rights to public. I have already ran the SQL query successfully:
"DENY VIEW SERVER STATE TO public" and "DENY VIEW DATABASE STATE TO public"

However when I check my master DB, the public still have rights to all the dm_***** objects. Am I doing it wrong or is there any steps I missed out? Can anyone help please?
Thanks a million.

View 1 Replies View Related

Public Report

May 7, 2008

Is there a way to make a report public, to outside your domain users? We have a web application the users are authenticated in that is not in .NET and not using NT authentication and we want to have a report linked from inside the application that will pass the parameters in the URL. This is not very sensitive data and the report is using SQL authentication. We also don't want then to log in a second time for the report.
Can this be done easily?

Thanks
Linda

View 6 Replies View Related

Use Of Public Variables

Feb 23, 2007

Hi,

I'm actually using global public variables in the custom code of my reports.

Is there a "chance" that , if the report is executed at the same time by two users, values calculated for an user will be crushed by other user's execution ?

Thanks in advance.

Grégory.

View 4 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...

ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;

I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;

View 18 Replies View Related

SQL Express On A Public Web Host

Mar 28, 2007

Are there any restrictions, limitations or anything to prevent the use of SQL Express database being used with my ASP.NET 2.0 application on a public web host?
If so, what are they?
Thanks.

View 10 Replies View Related

Error While Connecting To DB Through Public IP

Mar 16, 2004

While connecting to the DB through a public IP from my system i'm getting an SQL Server does not exist or access denied.But while using localhost its working fine.DB setting in the web.config file.The Code is


<appSettings>

<add key="connString" value="packet size=4096;user id=sa;data source=EBIZ;persist security info=True;initial catalog=ACE;password=password" />
</appSettings>

What to do for this error.

View 1 Replies View Related

Grant Rights To Public

Apr 26, 2001

Can I grant rights to "public" role ( customize it). Our app uses it.

thanks,

View 1 Replies View Related

Using SQL For Navigating A Public Web Site

Apr 4, 2000

Is anyone using SQL server to navigate their public web site?
We are concerned about having a database hit every time the user chooses a different page from our navigation bar.
Any advice? Experience with this?

View 2 Replies View Related

Public Database Permissions

May 2, 2007

I have a db that I use as the backend to an Access application. The application looks at 2 databases that are on the clinet machine. A db from a vendor and my new database. I can read the vendor db (with the proper dns), but I can only read my db when the user has admin privileges. I have granted the public all permissions for the table on my db. What am I missing?

Also, this is a db on a Small business Server 2003 so all of the SQL tools do not appear to be there.

TIA

View 2 Replies View Related

Create Public Synonym

Jul 15, 2006

I get an error in query analyzer when running (parsing query):

CREATE PUBLIC SYNONYM LIB_GROUP_PERMITS FOR LIB_GROUP_PERMITS;

with an error of:

"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PUBLIC'."

Can anyone help me at all please!

Thanks!

View 3 Replies View Related

How Do I Open Up Sql Server To The Public On Web

Feb 14, 2008

set up windows 2003 server
sql server 2000

have public IP from netword soluctions
I can see website

what settings to i need to open up sql server to the public?

View 12 Replies View Related

MS CRM Reports Not Working With Public IP

Mar 1, 2008

Hi,

I have installed a MS CRM 3.0 Server with SQL 2005 Reporting Server on the same server.

Whenever I access reports from MS CRM with internal IP i.e http://192.168.100.55:5555 within my network every default as well custom reports are working fine.

But the same when I access with Public IP i.e. http://209.199.X.X:5555 I am able to see the application in public i.p but the reports are not working.

Is there any way so that I can access my all reports from MS CRM in public IP.

Thanks

Kshitij

View 1 Replies View Related

Declaring Public Variables

May 30, 2007

I am trying to use variables that are declared in a report that I am created in SSRS 2005.

For example:

Declare @Month as int

Set @Month = case when month(getdate()) = 1 then 9 else month(getdate()) - 3

When I do this, I get an error message, 'The declare cursor SQL construct or statement is not supported.'

So how do i properly declare a variable that can be used through all my dataset?

Thanks, Iris

View 10 Replies View Related

The Public Group And Do We Realy Need It

Jan 3, 2007

Hi

I have a question, the public user, what rights does hen need on Master and MSDN we runn a Application Vulnerability Assessment and it became obvius that he has quite a few rights like adding jobs.

Any sugestions?

View 4 Replies View Related

Dt_ Procs And The Public Role

Oct 3, 2006

In a SQL 2k instance (latest SP) some of my user databases show the public role with execute on a variety of stored procs named dt_* (i.e. dt_addtosourcecontrol). However, not all the user databases do this, some do not grant the public role execute on these procs .

So, can someone explain what generates these permissions and is it acceptable to remove them? If I have a database that does not grant public access, should I be concerned? I don't see any reference to these procs in BOL.

TIA,

Moblex

View 1 Replies View Related

Copy Public Role

Aug 24, 2007

Is it possible to script the Public role? Delete authorities granted to Public?

I need to copy all the permissions of the Public role from one database to many others. The databases were setup incorrectly with many authorities granted to Public.

View 1 Replies View Related

Public Key Token = B77a5c561934e089

Aug 2, 2006

Hi Ppl,

I have installed my program and SQL server 2K5 on the server.

But when I try to run it (from he client machine)I get the following error :

It works fine on the SERVER

The appication attempted to perform an operation not allowed by the security policy.

The operation required the security Exception. To grant this application the required permission

please contact your systems Administrator or use the Microsoft .NET security policy administration tool.

If you click Bla,bla,bla

Request for the permision of type System.Data.SqlClient.SqlClient
Permission, System.Data Version - 1.0.5000.0. Cultre = neutral.

Public key Token = b77a5c561934e089

View 3 Replies View Related

Public Server Role

Mar 30, 2006

In exploring permissions that users have, I find that they all have VIEW ANY DATABASE permission which they inherit from the public server role. You can see this by selecting the Permissions page on the Server Properties dialog and highlighting "public". The permission shows as having been granted by sa. This is listed as a server role. However, it does not show in the list of server roles and I can't find any documentation for it (RTM BOL). Interestingly, if I revoke this permission (which is the only permission this role has), the public server role disappears from view. But I can subsequently regrant the permission with Transact-SQL in master and the role comes back.

I would like more information about this role. It seems to be sort of "secret".

When I revoke the permission, users can't see any databases except master and tempdb (both of which have active guest users) even though they have been granted access to other databases.

What I was trying to accomplish by changing this permission was to allow a user to see only those databases which they are allowed to use. But that does not seem to be possible.

Thanks for the help.

Sharon

View 5 Replies View Related

Removing Login's From Public Role

May 24, 2000

Hello all,

I'm having trouble copying my production database to a development version because I have a login user in the public role that is not a valid user. I can't find any reference to the login/user anywhere in my database or in NT security, on my server for that matter.

When I open the public role through Enterprise Manager, I can see the logins/users in the list, but the 'Remove' button is disabled. I also tried to use the stored procedure 'sp_droprolemember', but it says that 'public' is not a role in the database.

DTS keeps blowing up on this object when exporting, and I need to get this stuff copied ASAP.

Thanks for all help

Kevin

View 1 Replies View Related

Grant Select On All Tables To Public

Jan 17, 2001

This is a newbie question but, does anyone know how to grant select on all tables within a given database to public with a SQL statement.

View 1 Replies View Related

T-SQL - How To Grant Public Access To A DB For A User

Nov 2, 2007

I need an example of how to grant access to a SQL user to a DB. For the life of me I can't seem to get my syntax correct. My database name is TEST and my username is LEMME_IN and I want to grant the user "Public" access to the db with db_datareader, db_datawriter database role membership.

Thanks

View 1 Replies View Related

Srvr Permissions: Public Gone, Need It Back!

Jun 3, 2008

Hi all,

Hope someone can help: by default, there's an entry for 'public' in server permissions (right-click server in Management Studio > Properties > Permissions), which has 1 selection: 'View All Databases' is set to 'grant'. I wanted to disable this permission, and so unfortunately unchecked the 'grant' tickbox but forgot to tick the 'deny' tickbox. What appears to have happened is the entire 'public' entry has been removed, meaning I can't go back and reselect 'grant' (or 'deny') for 'View All Databases'. I'd really like to get the 'public' role to reappear... Is that possible?

A very big thanks for any help!

View 1 Replies View Related