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?
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.
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*
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.
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.
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
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.
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
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.
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
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:
[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)
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
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)?
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?
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.
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.
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 ?
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?
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.
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
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?
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
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.
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.
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?
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????
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?
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?
