TRUNCATE TABLE In A Sproc W/locked-down User
Sep 18, 2006
Hullo folks, I'm having what I assume is a fairly mundane security issue.
I have a SQL login that I am trying to restrict as much as possible. This account's sole goal in life is to hit the server, return some usage statistics, then truncate the table it received the statistics from. I would like to refrain from granting this login permissions on the physical target table if possible.
Usually I can wrap up "protected" operations in a stored procedure, then grant exec permissions for my user and I'm good to go. However, TRUNCATE TABLE gets cranky with me when I attempt the above method. I suspect that has to do with the fact that TRUNCATE TABLE is effectively modifying the table itself as opposed to merely deleting data.
Is it possible to grant this login ONLY execute permission on a stored proc that TRUNCATE's tables without giving the user any physical permissions? Am I going about this the wrong way?
View 7 Replies
ADVERTISEMENT
May 1, 2015
best possible way to provide truncate table permission to a SQL Server Database user (ddl_admin role cannot be granted to the user in my case)
View 3 Replies
View Related
Sep 6, 2006
I just brought SQL Server 2005 up on Windows Server 2003 a few days ago. Everything was going great up until a few minutes ago. I tried to connect using the Management Console and get the message
Login failed for user 'sa' because the account is currently locked out. Your administrator can unlock it.
It's the administrator that's locked out!
Do I have to reinstall SQL Server all over again?
View 2 Replies
View Related
Feb 3, 2015
Is there a way to identify what user has a locked row in a table?
View 2 Replies
View Related
Jul 23, 2005
I'm trying to determine which user has locked a given record from VB6.I know I can use sp_lock and sp_who, and match up the data to determinewhich users have locked records in my database, however I haven't seen a wayto match the specific user to the specific record. What am I missing here?Thanks!*David*
View 5 Replies
View Related
Oct 20, 2006
I have gotten my production version of SQL locked into single user mode. We recently upgraded to a clustered configeration and when I went to pause the system to get control I was told Pause was not available in a clustered environment. I restarted the service with -m [single user] but couldn't run Enterprise Manager as it appeared to take the only user slot and not give me one. Then I hit on startingup with Query Analyzer and that worked but now how do I get it back into multi-user mode. I placed the -m in the settings using Enterprise manager; could I stop the service and restart at the cmd prompt using -f [minimal config] and get Enterprise manager running? Any assistance appreciated.
View 4 Replies
View Related
Nov 29, 2006
Greetings,
I receive an error message in event log when i try to connect to the Database Server using ODBC on a client machine. The database server is running on Windows 2003 Server Standard Edition and the client machine is Windows XP Professional. Following is the error message from the event log:
2147467259 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.
What causes the error to occur and how to resolve it?Appreciate for your assistence.
Thanks and regards,
Viknes
View 4 Replies
View Related
Feb 6, 2008
And no, not through EM or SSMS
I want to interogate the catalog, so I can hae a job execute and do the grants in case a developer forgets
I mean I guess I can do the all everytime, but I don't know what the impact would be. It appears to be none, because of already done that, but in DB2 that would be bad as it would keep adding rows to the system tables
I have this so far
select *
from syspermissions p
inner join sysusers u
on u.uid = p.grantee
inner join sysobjects o
on o.id = p.id
where u.name = 'mepuser'
and o.name not like 'dt_%'
order by p.id
The M$ catalog is a royal pain
View 8 Replies
View Related
Jan 10, 2002
Hi,
When I am deleting records from a table, I am not able to select or insert records from the table. This particular table needs to be used by more users and very frequently used.
Could anyone please help with ideas.
Thanks in advance
John Jayaseelan
View 5 Replies
View Related
Nov 18, 2005
I can't open a table that has 5 records. Is there any kill processes that can unlock the table for me?
View 2 Replies
View Related
Feb 19, 2006
I am giving the following statement outside the begin tran block in a stored procedure:
select * from table1 with (holdlock) where column1 = @colValue
I have the following two questions regarding above situation:
Being outside the begin tran block, will the select statement cause a lock to be held or it has to be within the begin tran block for the lock to be held?
Will the select statement hold a lock only on the row or rows matching the where clause, or it may lock a page or even the entire table in this situation?
Thanks
View 3 Replies
View Related
Aug 23, 2006
Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 3 Replies
View Related
Sep 21, 2005
How can I determine when a sproc or table was last used?
I suspect that I have many obsolete tables and sprocs in my database but how can I find out for sure??
Thanks,
DL
View 5 Replies
View Related
Jan 4, 2005
To all,
Here's the error:
Input string was not in a correct format.
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.
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
Line 159:
Line 160:objConn.Open()
Line 161:objCmd.ExecuteNonQuery()
Line 162:objConn.Close()
Line 163:
Source File: C:FullerAviationSupplymanagecatalog.vb Line: 161
Stack Trace:
[FormatException: Input string was not in a correct format.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
managecatalog.SubmitProd(Object s, EventArgs e) in C:FullerAviationSupplymanagecatalog.vb:161
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
Here's the code from my vb file:
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Protected WithEvents dgProductCatalog As datagrid
Protected WithEvents tbxProductName As TextBox
Protected WithEvents tbxInventory As TextBox
Protected WithEvents dgCatProd_Active As DropDownList
Protected WithEvents dlvendor As DropDownList
Protected WithEvents dlSubcat As DropDownList
Protected WithEvents btnLinkvend As Button
Protected WithEvents tbxshortdesc As TextBox
Protected WithEvents tbxlongdesc As TextBox
Protected WithEvents qtyPA As TextBox
Protected WithEvents pricePA As TextBox
Protected WithEvents qtyPB As TextBox
Protected WithEvents pricePB As TextBox
Protected WithEvents qtyPC As TextBox
Protected WithEvents pricePC As TextBox
Protected WithEvents sizeX As TextBox
Protected WithEvents dlX As DropDownList
Protected WithEvents sizeY As TextBox
Protected WithEvents dlY As DropDownList
Protected WithEvents sizeZ As TextBox
Protected WithEvents dlZ As DropDownList
Protected WithEvents weight As TextBox
Protected WithEvents dlweight As DropDownList
Protected WithEvents dgInsertProduct As Button
Protected WithEvents thumbfile As TextBox
Protected WithEvents mainfile As TextBox
Dim objDA As SqlDataAdapter
Dim objDS As New DataSet()
Dim objDX As New DataSet()
Dim objDV As New DataSet()
Dim objDU As New DAtaSet()
Dim objDW As New DAtaSet()
Dim objDT As DataTable
Dim objDR As DataRow
Dim objConn As SqlConnection = New SqlConnection(ConStr)
Sub Page_Load()
If Not IsPostBack Then
BindData()
End IF
End Sub
Sub BindData()
objDA = New SqlDataAdapter("GETCATALOG",objConn)
objDA.Fill(objDS, "MainCatalog")
objDT = objDS.Tables("Catalog")
dgProductCatalog.DataSource = objDS
dgProductCatalog.DataBind()
objDA = New SqlDataAdapter("GETSUBCAT",objConn)
objDA.Fill(objDX, "SubCat")
dlSubcat.DataSource = objDX
dlSubcat.DataValueField = "SubCat_ID"
dlSubcat.DataTextField = "SubCat_Name"
dlSubcat.DataBind()
objDA = New SqlDataAdapter("GETVENDOR",objConn)
objDA.Fill(objDV, "Vendor")
dlvendor.DataSource = objDV
dlvendor.DataValueField = "VendorID"
dlvendor.DataTextField = "CompanyName"
dlvendor.DataBind()
objDA = New SqlDataAdapter("GETSIZEUNITS",objConn)
objDA.Fill(objDU, "Units")
dlX.DataSource = objDU
dlX.DataValueField = "Size_ID"
dlX.DataTextField = "Size_Unit"
dlX.DataBind()
dlY.DataSource = objDU
dlY.DataValueField = "Size_ID"
dlY.DataTextField = "Size_Unit"
dlY.DataBind()
dlZ.DataSource = objDU
dlZ.DataValueField = "Size_ID"
dlZ.DataTextField = "Size_Unit"
dlZ.DataBind()
objDA = New SqlDataAdapter("GETWEIGHTUNITS",objConn)
objDA.Fill(objDW, "Units")
dlweight.DataSource = objDW
dlweight.DataValueField = "weight_unitID"
dlweight.DataTextField = "weight_Unit"
dlweight.DataBind()
End Sub
Sub SubmitProd(s As Object, e As EventArgs)
Dim objCmd As New SqlCommand("INSERTPRODUCT", objConn)
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@prodName", tbxProductName.Text)
objCmd.Parameters.Add("@descShort", tbxshortdesc.Text)
objCmd.Parameters.Add("@descLong", tbxlongdesc.Text)
objCmd.Parameters.Add("@qtyPA", SqlDbType.int ).Value = qtyPA.Text
objCmd.Parameters.Add("@qtyPB", SqlDbType.int ).Value = qtyPB.Text
objCmd.Parameters.Add("@qtyPC", SqlDbType.int ).Value = qtyPC.Text
objCmd.Parameters.Add("@pricePA",SqlDbType.money).Value = pricePA.Text
objCmd.Parameters.Add("@pricePB",SqlDbType.money).Value = pricePB.Text
objCmd.Parameters.Add("@pricePC",SqlDbType.money).Value = pricePC.Text
objCmd.Parameters.Add("@vendorID", dlvendor.SelectedItem.Value)
objCmd.Parameters.Add("@inventoryAvail", SqlDbType.int).Value = tbxInventory.Text
objCmd.Parameters.Add("@imageThumb", thumbfile.Text)
objCmd.Parameters.Add("@imageLarge", mainfile.Text)
objCmd.Parameters.Add("@sizeX", sizeX.Text)
objCmd.Parameters.Add("@sizeY", sizeY.Text)
objCmd.Parameters.Add("@sizeZ", sizeZ.Text)
objCmd.Parameters.Add("@sizeUX", dlX.SelectedItem.Value)
objCmd.Parameters.Add("@sizeUY", dlY.SelectedItem.Value)
objCmd.Parameters.Add("@sizeUZ", dlZ.SelectedItem.Value)
objCmd.Parameters.Add("@weight", weight.Text)
objCmd.Parameters.Add("@prodActive", dgCatProd_Active.SelectedItem.Value)
objCmd.Parameters.Add("@weightU", dlweight.SelectedItem.Value)
objCmd.Parameters.Add("@SubCat_ID", dlSubcat.SelectedItem.Value)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
tbxProductName.Text=""
tbxInventory.Text=""
tbxshortdesc.Text=""
tbxlongdesc.Text=""
qtyPA.Text=""
pricePA.Text=""
qtyPB.Text=""
pricePB.Text=""
qtyPC.Text=""
sizeX.Text=""
sizeZ.Text=""
sizeZ.Text=""
weight.Text=""
BindData()
End Sub
And here's the Stored Procedure:
CREATE PROCEDURE INSERTPRODUCT
(
@prodName nvarchar(50),
@descShort nvarchar(3000),
@descLong nvarchar(4000),
@qtyPA int,
@qtyPB int,
@qtyPC int,
@pricePA money,
@pricePB money,
@pricePC money,
@vendorID int,
@inventoryAvail int,
@imageThumb nvarchar,
@imageLarge nvarchar,
@sizeX nvarchar,
@sizeY nvarchar,
@sizeZ nvarchar,
@sizeUX int,
@sizeUY int,
@sizeUZ int,
@weight nvarchar,
@prodActive int,
@weightU int,
@SubCat_ID int
)
AS
INSERT INTO ProductCatalog (ProductName, Desc_Short, Desc_Long, qty_PerA, qty_PerB, qty_PerC, Price_PerA, Price_PerB, Price_PerC, VendorID, InventoryAvail, image_Thumb, image_Large, Size_X, Size_Y, Size_Z, SizeUnit_X, SizeUnit_Y, SizeUnit_Z, weight, Prod_Active, weight_unit, SubCat_ID) VALUES (@prodName, @descShort, @descLong, @qtyPA, @qtyPB, @qtyPC, @pricePA, @pricePB, @pricePC, @vendorID, @inventoryAvail, @imageThumb, @imageLarge, @sizeX, @sizeY, @sizeZ, @sizeUX, @sizeUY, @sizeUZ, @weight, @prodActive, @weightU, @SubCat_ID)
GO
Any help would be greatly appreciated.
Regards,
Seth
View 2 Replies
View Related
May 2, 2005
Okay, here's the scenario....
I have a bunch of un-related tables that I need to make relational.
I have a bunch of SProcs set up to help with the relating of the tables but I need the quickest way to get the values from the tables and into the SProcs.
Currently, I'm using .NET to cycle through the necessary tables, sending in their values into the SProcs, one at a time.
I'd like to have some SQL-ized way to do this so I can just make another step in my DTS package that already copies over the un-related tables from DB2.
What I'd imagine the code to look like would be something like...
EXEC SP_EMPLOYEES (SELECT FIRST_NAME,LAST_NAME FROM oldEmployees)
I know, now, that it's not as easy as that but I'm thinking there MUST be a way - even if it requires creating a new SProc.
TIA
View 1 Replies
View Related
Apr 7, 2008
Hi,
I've got 2 tables
Table_A with 2 fields: ThreadID & Rating
This Table is updated as users rate forum threads.
Then I have Table_B which should display the some info in a Gridview
Table_B has 3 fields: Name, ID and Score
I want the Rating from Table_A to update to Table_B's Score Field.
I suppose I should write a sproc to do this, but I am not quite sure how;
This is what I would like the sproc to do...
Update Table_B
SET Score = Table_A.Rating WHERE ID = Table_A.ThreadID
Any help on this...or is there a should I not use a sproc.
Also...how do I get the sproc to update the table automatically (without me running the sproc manually)?
View 10 Replies
View Related
Jul 4, 2007
i did a Stored Procedure to truncate a table. it contains lots of crap from testing the site. just a simple 1 ALTER PROCEDURE dbo.TruncateUploads
AS
TRUNCATE TABLE Uploads but it says "Cannot truncate table 'Uploads' because it is being referenced by a FOREIGN KEY constraint." then i make NULL all foreign keys and try still cannot. then i did it manually deleting all records then can! but why can't i truncate it? isn't it the same as removing all values plus having the primary keys to start from 0?
View 5 Replies
View Related
Oct 27, 2007
How can i TRUNCATE my table (removes rows that were produced at testing), so that in the actual running the automated IDNumber for my table start with 1.
View 6 Replies
View Related
Nov 1, 2000
hi, I have two tables members table and orders table.
I have 2 records in members table and zero records in order table.
When I try to truncate members table I get an error message.
cannot truncate table members,because it is being referenced by a FOREIGN KEY constraint.
this what blows my head, There is no records in orders table. so why would the constraint enforced when there is no data in the order table. I created same table in access and I was able to delete a member from the members table. any comments.
thanks
Ahmed
View 4 Replies
View Related
Feb 1, 2005
I have several very large tables and sometimes I need to clean them.
It's known that TRUNCATE TABLE works much faster than DELETE, but impossible to use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint... Is it possible temporary disable (not delete) constraints and after complition of TRUNCATE enable them ?
View 14 Replies
View Related
Feb 10, 2005
Hi,
(From Bol)
"You Cannot Use Truncate Table On A Table Referenced By A Foreign Key
Constraint;"
1) States
Statecode TinyintPrimary Key
Statename Varchar(25)
2) Rivers
RivercodeSmallint Primary Key
RivernameVarchar(40)
3) Riverinstates
StatecodeTinyint,
RivercodeSmallint,
Constraint Pkriverinstates Primary Key(Statecode,Rivercode),
Constraint Fkriverinstates
Foreign Key (Statecode) References States(Statecode),
Foreign Key (Rivercode) References Rivers(Rivercode)
after removing all rows from RiverInStates Table using TRUNCATE and the command completed successfully.
when i tried to remove all rows from Rivers table , i got the following error.
"Server: Msg 4712, Level 16, State 1, Line 76
Cannot truncate table 'rivers' because it is being referenced by a FOREIGN
KEY constraint."
i thought the error may be due to the "Fkriverinstates" FOREIGN KEY CONSTRAINT. so i DROPped the constraint with
ALTER TABLE RiverinStates DROP CONSTRAINT Fkriverinstates
even after DROPping the constraint, im getting the same error
can any one point out where the problem is?
thanks in advance
View 1 Replies
View Related
May 18, 2004
what is the best way to truncate/delete records from a table at a certain time of the day...... i'm sure this is a simple ?, but i'm still learning sql.
thanks,
eddie
View 4 Replies
View Related
Jan 18, 2007
any idea why this is not wroking ?
------------------------------
declare
@TableName varchar
DECLARE
EmptyTables CURSOR FOR
Select Table_Name From Information_Schema.Tables
Where Table_Type = 'Base Table' Order By Table_Type
open EmptyTables
FETCH NEXT FROM
EmptyTables
into
@TableName
WHILE @@FETCH_STATUS = 0
BEGIN
truncate table @TableName
FETCH NEXT FROM
EmptyTables
into
@TableName
END
close EmptyTables
deallocate EmptyTables
View 11 Replies
View Related
Nov 16, 2006
I have a database of 25 GB. I truncated a table which holds around 5 GB of data.
Even after truncation, I see my database size as 25 GB...
Should I run a shrink database after the truncation?
Thanks
Santhosh
View 3 Replies
View Related
Oct 5, 2007
Hi. This is a SQL question.
I am trying to wrap a stored procedure with a UDF so I can do selects against the result. The following doesn't work, but is it possible to do something like:
Create Function test()returns @tmp table ( myfield int)asbegin insert into @tmp (field1) Exec dbo.MySprocWhichRequires3ParmsAndReturnsATable 5, 6, 10 output returnend
Thanks
View 1 Replies
View Related
Dec 12, 2007
My goal is to recreate a table daily so that the data is updated. This could be a bad decision performance-wise, but I felt this was simpler than running a daily update statement. I created a stored procedure:SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_CreatetblImprintPhrase
AS
DROP TABLE tblImprintPhrase
GO
CREATE TABLE tblImprintPhrase
(
CustID char(12),
CustName varchar(40),
TranNoRel char(15)
)
GO However, I was looking to edit the stored procedure, changing CREATE to ALTER, but when I do so, I am prompted with: Error 170: Line 2: Incorrect syntax near "(". If I change back to CREATE, the error goes away, but the sproc cannot be run because it already exists. Any thoughts?
View 2 Replies
View Related
Jun 10, 2008
I have a table with this structure
ID | Ticker
-------------------
1330 |AAB-Bank
1336 |AEGON
1367 |ALZSE
1420 |ASSGEN
2812 |AVLN
I have a sproc called usp_validTicker that will take 2 parameters: ticker and date. It will return the valid ticker for that date.
I like to have the sproc going through each ticker in the table and return the valid tickers.
For example
exec usp_validTicker 'AAB-Bank','2008-6-10' will return 'AAB' and my final table will be
ID | Ticker
-------------------
1330 |AAB
1336 |AEGON
1367 |ALZSE
1420 |ASSGEN
2812 |AVLN
View 13 Replies
View Related
Sep 21, 2005
Hi. It seems to be very simple, actually, but I don't know if it isfeasible in TSQL. I have a sproc which gathers in one place many callsto different other sprocs, all of them taking a 'StoreGroupe'parameter. I would like to add a case where if the call has NOStoreGroupe parameter, the sproc should LOOP thru all records in tableStoreGroupeTable, read the column StoreCode, and pass that value as aparam to the other sprocs, as in:CREATE PROCEDURE MySproc(@StoreGroupe nvarchar(6) = NULL)ASif (@StoreGroupe is not null)BeginExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............EndElseBeginA 'Group Code' has NOT been specifiedI want to take all the StoreGroups in tableStoreGroupeTable, in turn.I would like to do SOMETHING LIKE THIS:Do While not [StoreGroupeTable].EOFRead [Code] from [StoreGroupeTable]Set @StoreGroupe = The value I just readExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............LoopEndGOIs that feasible in a sproc, or do I have to do this in the client(ADO) ?Thanks a lot.Alex.
View 4 Replies
View Related
Jul 16, 2001
I have somme users who need to truncate some tables owned by DBO. I know that only table owner or DBO can execute TRUNCATE TABLE but I don't want grant DBO permission to those users. Do you have any suggestion ? Thanks a lot.
View 4 Replies
View Related
Jul 23, 2001
If I have a truncate table statement in a stored proc, will my log backups be compromised due to the nonlogged operations. If so, what are my alternatives in case that I need to restore? Differential backups?
View 1 Replies
View Related
Apr 13, 2000
I am doing the following-
begin transaction
truncate table Acounts
select * from Accounts
rollback
select * from Accounts
The first select is returning 0 rows as expected. But after doing the rollback I expected to yet see 0 rows as truncate is not logged so cannot be rolled back. But to my suprise I see that there are rows.
So the rollback is rolling back the truncate table statement. Can someone please explain this????
View 4 Replies
View Related
Mar 17, 2000
I know that only the owner of a table can truncate it.
I want a user to run a procedure that truncates a table. Both the procedure and the table are owned by dbo, however the system says that the user can't truncate the tables because she doesn't have permission. The user has dataread and datawrite global permissions, and I do not want to give her dbo perimssions.
Is there a solution to this?
View 1 Replies
View Related
Aug 12, 1998
Hi all,
Is it possible to TRUNCATE a table and BCP data into the same table in one
TRANSACTION?
My problem is that I want to refresh(delete and via BCP append new data) a
table without disturbing running applications. Can I run BCP from a
SQL-script or a stored procedure?
Thank`s
Jonas Dahlqvist
Alfa Laval Automation AB
View 1 Replies
View Related
