Where Can I Get Undocumented Stored Procedures,Is It Safe To Use Undocumented Sp's.
Feb 28, 2008Hi All
Undocumented SP's like 'Sp_Msforeachdb',.
What are all the USP's availble,how for it is safe to use SP's
ADVERTISEMENT
Undocumented System Stored Procedures
May 15, 2007I know there are a lot of undocumented system stored procedures such as xp_ntsec_enumdomains, xp_instance_regread etc, that exist on SQL Server.
Does anyone know of any good websites that contain descriptions of what these stored procedures do? In particular I know that a default installation of SQL Server 2000 leaves permission to execute many of these granted to public and I am interested in finding out what the implications of each one of these are? I have tried Googling this topic but there doesn't seem to me much or there (or what is there is in Chinese and I don't really want to click on to!)
Once again thanks for the help.
Undocumented System Procedures
May 21, 2015I am trying to determine which are undocumented system procedures used to satisfy our company's security audit. Well, the nature of undocumented system procedures is it's undocumented. Hence, it's hard to find.
So, is there a way I can get a list of SQL Server documented system procedures?
Undocumented Sp & Xp
Jul 24, 2002Does anyone know where I can get information about undocumented stored procedures and extended stored procedures? I found 'xp_dirtree' and I think I've figured out how to use this one but there are others and I'd like some insight into what they do.
Sidney
Undocumented Feature
Jan 21, 2004Dig this....
USE Northwind
GO
CREATE TABLE ItemInformation([Description] varchar(80))
GO
INSERT INTO ItemInformation([Description])
SELECT 'CHOCOLATE CHIP‚' UNION ALL
SELECT '‚COOKIES‚' UNION ALL
SELECT '‚CROISSANTS *PLAIN*‚' UNION ALL
SELECT '‚DONUTS‚' UNION ALL
SELECT '‚DONUTS *DOZEN*‚' UNION ALL
SELECT '‚MUFFINS‚' UNION ALL
SELECT '‚BAGELS‚' UNION ALL
SELECT '‚ROLLS‚' UNION ALL
SELECT '‚CUPCAKES‚' UNION ALL
SELECT '‚CRISPIES‚' UNION ALL
SELECT '‚DANISH/SWEET ROLLS‚' UNION ALL
SELECT '‚FUDGE BROWNIES‚' UNION ALL
SELECT '‚PUFF PASTRIES/ECCLES‚' UNION ALL
SELECT '‚STICKY BUNS‚' UNION ALL
SELECT '‚TURNOVERS‚' UNION ALL
SELECT '‚BLACK & WHITE COOKIES‚' UNION ALL
SELECT '‚LINZER TARTS‚' UNION ALL
SELECT '‚SCONES/BISCUITS‚' UNION ALL
SELECT '‚SCUFFINS‚' UNION ALL
SELECT '‚SINFULL BITS‚'
GO
SELECT * FROM ItemInformation
GO
UPDATE ItemInformation
SET [Description] = REPLACE([Description],',','')
GO
SELECT [Description], LEN([Description]) FROM ItemInformation
GO
SELECT REPLACE([Description],',','')
FROM ItemInformation
SELECT REPLACE([Description],'C','')
FROM ItemInformation
SELECT CHARINDEX(',',[Description])
FROM ItemInformation
GO
DECLARE @x varchar(80)
SELECT @x = '‚COOKIES‚'
SELECT @x
SELECT REPLACE(@x,',','')
GO
DELETE FROM ItemInformation
GO
INSERT INTO ItemInformation([Description])
SELECT 'CHOCOLATE, CHIP‚' UNION ALL
SELECT 'CHOCOLATE, CHIP‚' UNION ALL
SELECT ',CHOCOLATE, CHIP‚' UNION ALL
SELECT ',CHOCOLATE, CHIP‚ ' UNION ALL
SELECT ',CHOCOLATE, CHIP‚ A' UNION ALL
SELECT ',CHOCOLATE, CHIP‚ , '
GO
SELECT REPLACE([Description],',','')
FROM ItemInformation
GO
DROP TABLE ItemInformation
GO
BIZZARO WORLD
Undocumented Error Code !!
Jun 25, 1998I found a error that is not documented when i run a long SP.
Error 3621 its not in the books
and any one help me ???
thanks
What Does The (seemingly Undocumented) FileNameRetrieval Property Of ForEachLoop Container Do?
Oct 4, 2007Just wondering.
View 1 Replies View RelatedStrange Situation With Windows Authentication And Undocumented Function GET_SID
Dec 21, 2004SQL Server 2000
Windows 2000 Advanced Server
We recently moved our servers from one domain to another. Now, we can't grant Windows users access to any databases. We CAN create the user IDs successfully via Enterprise Manager, and get a corresponding row in MASTER..SYSXLOGINS, but can't click on the Database Access tab in the SQL Server Login Properties dialog and grant accesss to any databases. When we try to do so, we get error 15401, "Windows user or group xxx not found". The underlying call is to MASTER..SP_GRANTDBACCESS, and running that via Query Analyzer returns the same error (naturally)
Looking through the code of SP_GRANTDBACCESS, I've determined that what is failing is a call to the undocumented TSQL function GET_SID. This proc takes two parameters, the first is either G<nt group name> or U<nt user name> and the second is NULL in the call in SP_GRANTDBACCESS. If I execute
SELECT GET_SID('U<valid user>', NULL)
it returns NULL, however, if I run
SELECT SUSER_SID(<valid user>)
then I get the Windows SID of whatever valid user name I supply.
We have 3 servers in question, namely production, development and test. We noticed the problem on production. Curiously enough, development and test worked fine.
NOW THE PLOT THICKENS. If I run this query on the dev box....
SELECT SUSER_SID(<id>), GET_SID('U<id>',NULL)
...the first function returned the SID, BUT THE SECOND FUNCTION DID NOT!!! How could that be? Clearly GET_SID was working inside of SP_GRANTDBACCESS, but not as a discrete call. So I went into the master database and added code to print out the SID returned by GET_SID to the proc. Lo and behold, SP_GRANTDBACCESS promptly failed with a 15401 error. It continues to get 15401s now, even after I returned it to the original code. What gives? Now my dev box has the same error production has, and all I did was recompile SP_GRANTDBACCESS a couple of times. FWIW, I did *not* ever make any changes to SP_GRANTDBACCESS on production.
Why doesn't GET_SID() work outside of SP_GRANTDBACCESS?
Why did recompiling SP_GRANTDBACCESS break it permanently?
It almost seems like the query compiler can't correctly link a call to GET_SID to the correct function in some DLL, except I thought that the compiled code didn't survive a restart, and all TSQL procs were recompiled the first time they were called after a restart. If that's the case, then the compiler is - or at least was - producing a functioning compiled version of SP_GRANTDBACCESS after every restart.
Stored Procedures In Visual Source Safe
Nov 15, 2007
I have about 40 stored procedures in a Visual Source Safe stored procedures which we are using for change control.
All stored procedures require to be moved into production across at least 13 different databases each on a dedicated server.
The only way I know at the minute is to do this manually. Does anyone out there have any ideas of how this task can be automated?
Using Stored Procedures Are You Safe From Sql Injection If Your Not Using Dynamic Queries ?
Mar 12, 2008Im reviewing my stored procedures for a new application and got to thinking about protecting against sql injection. I think im pretty safe since im using stored procedures and none of them use any 'exec' commands within them, but im not sure.
I was reading this article, and again all the examples that list a stored procedure, have an 'exec' command somewhere that is the culprit. So, in my case lets say I was doing something like this:
Im generally using regularexpression validation controls on the client side of the application and limiting the max length of the input there as well.
Am I safe, or do I need further input checking within the procedure ?
Code Snippet
CREATE PROCEDURE [dbo].[get_Uploads]
@app varchar(50)
--Init variables
SET @error_number = 0
BEGIN TRY
SELECT [Logid],[Filename],[Label],[UploadDate],[App]
FROM UploadLog au
WHERE [App]=@app
END TRY
BEGIN CATCH
SET @error_number = -2
END CATCH
Stored Procs And Source Safe Good Idea?
Sep 25, 2000Hi,
Any pros and cons of putting sprocs into Source Safe?
Thanks,
Judith
Visual Source Safe Data Cannot Be Accessed Through SQL CLR Stored Procedure
May 15, 2008The goal is to address visual source safe database on the network. We have the srcsafe.ini in the network as \ipaddrsrcsafe.ini. Now I create a new VSSDatabase object and call its OpenDb. Well for simple consle app or winform it is ok. But I was running it under Sql server Stored Procedure. It failed for I cannot access the source safe path throgh the COM object.
I know it is because of Windows identity. So I add the following code before I want to open the database, changing the to the WindowsIdentity:
WindowsIdentity impersonId = SqlContext.WindowsIdentity;
WindowsImpersonationContext orgCtx = null;
try
{
orgCtx = impersonId.Impersonate();
VSS_Database = new MVSI.VSSDatabase();
// VSS_Database.ImpersonateCaller = true;
VSS_Database.Open(Path, UserName, PassWord);
}
catch (Exception err)
{
orgCtx.Undo();
throw err;
}
finally
{
orgCtx.Undo();
}
Without the commented line "// VSS_Database.ImpersonateCaller = true", this does not work at all. It just behave like no changes to the windows identity.
However if I add this code, well, OpenDb will result in a No-response query. The Sql server is running the query with no responses.
Have you ever met that before? I am really frustrated. Thanks
Oracle Stored Procedures VERSUS SQL Server Stored Procedures
Jul 23, 2005I 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 RelatedStored Procedures 2005 Vs Stored Procedures 2000
Sep 30, 2006Hi,
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
All My Stored Procedures Are Getting Created As System Procedures!
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
How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures
Apr 29, 2008How 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'
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
Jun 13, 2007Seems 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 RelatedHow To Save Stored Procedure To NON System Stored Procedures - Or My Database
May 13, 2008Greetings:
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!
Stored Procedure Being Saved In System Stored Procedures
Apr 7, 2006We 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!
How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?
Apr 23, 2008Hello 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.
SSIS And Stored Procedures Results Stored In #Tables
Mar 26, 2008Hello
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
MS SQL Stored Procedures Inside Another Stored Procedure
Jun 16, 2007Hi,
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
-------
Calling Stored Procedures From Another Stored Procedure
May 8, 2008I 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
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi 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?
Run DTS In Safe Way.
Jul 23, 2005I have two servers: progress as transational server and mssql as warehouseserver.I did DTS that "pumps" data from progress to mssql (via ODBC). Copying thedata has to be done once a day, but sometimes there is a need to do it onuser's demand.I'd like to ensure that I properly understood the method of running DTS I'vejust found using Google.As I can see, there are at least two methods (except of scheduling):- using dtsrun - which requires user running the DTS to have adminprivileges to use xp_smdshell- using sp_start_job - which requires creating a job prior to running theDTS.I think of using the second one as it seems to be more secure - am I right?Are there any hidden traps? What else should I do?--PL(remove "nie.spamuj.bo.w.ryj" from my email address)
View 2 Replies View RelatedHow Safe Is RS?
Apr 8, 2008I am trying to persuade our security people to allow access to RS from external facing web sites. The web sites will talk to the SOAP interface over SSL so the firewall will need to open port 443 between the web server in the DMZ and the RS server inside the firewall.
Any credentials passed will be encrypted, so the main questions is around parameters to reports and the possibility of SQL Injection attacks. I need a white paper or other document that will convince them that RS will not allow SQL Injection or scripting attacks and properly validates parameters.
Does any such thing exist?
Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod
May 15, 2008i 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
SP & Source Safe
May 3, 2001Can anyone tell me how I can copy store procedures from SQL Server 2000 to Source Safe 6.0? I right-click the store procedure and cop
y it but when I got to the visual source safe it will not work. I therefore went through the soruce code control on Tool menu which I cannot see such commands. I do really need your help.
Regards,
SQL Connection : Is It Safe?
Oct 27, 2007I've recently planned to make a program that uses remote MSSQL connection. I'm not such an expert in security.
Please can someone tell me if it safe? and how can I improve my security. I know how to block SQL injection, but me fear is that some will hack my software for personal use.
Thanks, RedEyez.
Safe Way To Cast
Nov 29, 2007I have a table that stores a value called "LocationNumber" as a varchar. I didn't create the database, I just use it. Anyway I usually sort the list by Casting the LocationNumber to an Integer. If I don't cast it, it trys to sort it lexiographically i.e. 0, 1, 21, 3, 345, 9 instead of 0, 1, 2, 3, 9, 21, 345.
It works 99.9% of the time, but the other 0.1% of the time it runs into some text that somehow made its way into the database field, and it causes an error.
Is there a way to safely cast a number in SQL? So that it just returns 0 when the value is not a number.
Safe Columns Names
May 28, 2004Hey,
I'm creating registration form.
To show fields names I thought to read columns names.
It's ok if columns is named like "Name", "Age" etc.
But if the columns is named [Country, Address, PostCode] then, I think, it can course some problems. Am I right?
First problem I thought about - changing database in the future (Now MS SQL 2k to MySQL etc.)
Is this the only problem?
To solve this I think using table which store syscolumn names as user defined columns names.
My system is speed critical and using this I would get less performance.
Which way should I go?
Case saving columns names in table, how to generate safe column name from user specified name, which can have special charters.
Thanks
Source Safe And SQL Server 7.0
Oct 11, 2000I'm in the process of locking down our SQL Server development environment and wanted to implement some type of version control, not just for stored procedures but for all database objects (tables, triggers, etc.)
A while back I read an article that explained how to utilize Visual Source Safe to establish version control for databases. If I recall correctly, I believe it had something to do with Visual Interdev and creating some project with Visual Interdev.
If anyone can provide alittle more insight into how to accomplish this, or at least point me in the right direction, it would be greatly appreciated.
Thank you in advance for your help!
SQL-safe String Inserts
May 13, 2008We have a VB.Net 2005 application that uses SQL CE 3.1 as its embedded database.
Frequently in the application, we must store strings with apostrophes, quotes, and all kinds of other stuff. It's totally unwieldy to try and manually escape every nonstandard character in every string... this is why we need to know how to handle this issue for all possible input.
What is the best method we can use to store any string, no matter what characters occur in it? The reason we must now improve our string handling is that we are now being required to store MD5 hashes of files for security and duplicate file avoidance, and these hashes usually break our import functions.
We normally enclose strings in single quotes ('). But, with the hashes as mentioned above, none of our current code works. Again: how can we be certain that the exact string we pass in will be stored in its current form, no matter what the characters?
THANKS