Security Stored Procedures
Feb 26, 2004Hello, everyone:
How to security the stored procedures? I want to prevent the stored procedures to be changed accidently. Thanks.
ZYT
ADVERTISEMENT
Stored Procedures/security
Jul 23, 2005First off I am a rookie at Sql Server. Ok let's give this a try. Mycompany has bought a new software package called Viewpoint. It's OnSql Server and written in VP. We do not have access to the code.There is a option in the software package called "ApplicationSecurity". When this is clicked you are unable to access the ViewpointDatabase from an outside application. Since I can't get to the code Ihave no idea how this feature works.I would like to keep the "Application Securty" ON so no one can accessthe Viewpoint Database from an outside application but I would like towrite my own outside application where I can access the Viewpointapplication tables while the Application Security in ON. Does anyoneknow what I am talking about? I know it is not very clear and I amworking with limited information, but any help would be appreciated.Thanks
View 1 Replies View RelatedStored Procedures, Security, Xp_cmdshell
Sep 27, 2000To try to secure an outside web application we set up a user that
only has permission to execute a series of stored procedures that are
related to the appliation. Unfortunately a couple of those stored
procedures have to access system resources outside SQL Server so we
are using a call to xp_cmdshell from inside the stored procedure
SQL Server apparently won't let us do that unless we give our
restricted user (who is calling the initial stored procedure) execute
permission on xp_cmdshell. This, of course, negates most of the benefit
of setting up a restricted user. Is there some simple way I am missing
of running xp_cmdshell from inside s stored procedure without the user
calling the stored procedure having execute permission on xp_cmdshell?
How To Provide Security To Stored Procedures
Jul 20, 2005Hi all,I know that it is possible to encrypt Stored Procedures using 'withencyption'.But my problem is that when there are so many decriptingmethods available how far will the encyption be secure.Is there any other method to encrypt the stored procedures that areresiding on the customer sites.We do not want the customers to meddle with the SPs.If anyone knows can u please let me know.ThanksDilini
View 1 Replies View RelatedSecurity, Dynamic SQL, And CLR Stored Procedures
Aug 1, 2006Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. I'm suspect there's a simple answer, but I'm unaware of it. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is impersonating a low-privilaged Windows account ("UserX") coming from a .NET application. UserX has no permission to the table referenced in the dynamic SQL and because of the dyanmic nature of the query, the stored procedure apparently adopts the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may use the same connection, and be as secure, but simpler.
Disabling Extended Stored Procedures For Security
Jun 29, 2007Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?
View 5 Replies View RelatedControlling Security Through Stored Procedures -- 2005 Behaviour
Aug 23, 2005Hi!
View 4 Replies View RelatedWhat Is The Security Setting To Allow Editing Of Stored Procedures From Management Studio?
Nov 28, 2007Greetings
Running SQL Server 2005. The developers on the project can see and edit stored procedures from within the Visual Studio IDE (via Server Explorer) but when they connect through management studio, the stored procedures do not show up at all.
Is there a seperate security setting specifically for management studio?
The user has:
The dbCreator Server Role
Is mapped to the development database as dbo with datareader/datawriter/db owner/public role
Is mapped to master reader/writer/public
Is mapped to model reader/writer public
Is mapped to msdb reader/writer public
Is mapped to tempdb reader/writer publuc
This is probably more security than the user needs, but was grasping at straws to let them edit stored procedures...
SQL Security :: Deny Alter And Drop Permissions On ONLY Stored Procedures
Aug 19, 2015We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them).
View 17 Replies View RelatedApplication/Security Design: Stored Procedures Versus SQL Queries
Mar 7, 2007Hello everyone,
I don't know what category would be appropriate for this question but security seems to be close enough.
I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.
Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.
Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?
No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.
Any thoughts or ideas?
Thanks for your time, Adrian
SQL Security :: How To Disable User Access To The Critical And System Stored Procedures
Sep 22, 2015disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?
View 5 Replies View RelatedOracle 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
SQL 6.5 Security For Store Procedures
Sep 30, 1999How would you set up a group of developers-application programers in SQL 6.5 to let them have authority so that they all can store, update, delete, & execute each others stored procedures, within a particular database.
They are not permitted to modify the table structures within a data base, but I can not seem to let them have authority so that
they can work on and execute any of their sp's unless the DBO actually does the sp modifications? They do not want to modify any code by putting the sp owners name in front of the sp name (I don't blame them), otherwise Error 2812 results.
TIA for all you responses
Nested Procedures && Using Signature Based Security
May 17, 2006I am currently developing a project that requires a server level permission for one stored procedure (ALTER ANY LOGIN)
To this effect, I plan to create a certificate, sign the stored procedure with it, import the certificate into the master DB and assign privileges.
I also understand that modification to the code invalidates the signature (after all thats the point of signing something).
But what about user defined functions and stored procedures referenced by the signed procedure? Does SQL server follow the dependancy chain and include referenced procedures in the signature? Or does the privilege assigned to the certificate not apply when the nested procedure is executed?
If this is not the case couldn't a restricted database user just alter a nested stored procedure they have been granted ALTER access to and make themselves SA or something?
So to sum up, do you have to duplicate the functionality of otherwise nested procedures into a certificate signed procedure to protect server security?
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?
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
Stored Procedure - Security
Dec 22, 2006Is it a safe way to use a paramter (which fetch values from querystring) in the "where-part" of my stored procedure? Or is it an securityrisk because I dont know what the user is writing in the url-field?
I got the following sqldatasource which grab the value (from querystring) into the my parameter.
<asp:SqlDataSource ID="SQLDataSource"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
runat="server"
SelectCommand="My_StoredProcedure"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter QueryStringField="Myparameter" Name="City" Type="string" />
</SelectParameters>
</asp:SqlDataSource>
Security On Stored Procs On Dev Db
Mar 25, 2002I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?
can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?
this is sql7, sp3, development box.
thanks,
Stored Procedure Security?
Oct 31, 2007Hi all,
this should be a easy question, but I can't really seem to find anything on it...
Here's the scenerio:
n-tier web app, with asp/iis/sql... All database calls are done via stored procedures with the same user (lets call the user: webuser)
webuser has NO access to the db in question, but it is granted EXEC on all stored procedures.
My question is, when a user tells the web app to say delete a record, the application server (iis) makes a call to the database with the webuser security cred's... It says execute the delete stored proc.
webuser has the ability to do this, so it happens. However, in what context (this may not be the right word) does the stored procedure execute?
ie: which user does the stored proc exeucte as. It can't be webuser can it? Because webuser does not have access to the base tables.
Does the stored proc execute as the user that created it?
I'm confused...
thx all!
Stored Procedure Security
Mar 19, 2008Hi,
I have a Stored Procedure in one database that grabs data from another database. I don't want the user to be able to read data from the tables that the stored procedure Selects from but I would like the user to be able to run the stored procedure. Pretty standard request I think.
What I have done is to give the user in question a login then assigned them the Execute Permissions on the stored procedure. Unfortunately they are still unable to run the stored procedure from my web app. I have "allow anonymous access" turned on but I am still getting an error when the user tries to execute the stored procedure.
Am I missing something here or could there be a bigger issue?
Thanks,
Patrick
Stored Procedure Security
Sep 4, 2007
Hi,
I have a stored procedure spoc_CreateNewUser.
I have a role called 'creator'. I want that no one else other than creator should be able to execute this procedure through an asp.net application or directly.If anyone attempts, it should be logged.
HOw do I do this. Please explain the answer. I am new to this.
Ron
Where Is Security Info Stored?
Apr 7, 2008How does SQL Server store Security Information. Accounts, Users, etc... Is there a particular table, like Master Table?
View 4 Replies View RelatedCLR Stored Procedure Security
Jan 11, 2008Hi,
I created a CLR stored procedure, and added a web service reference, am using the generated proxy class
to call the web service, currently am facing the following security issues. I think am missing
something, what are the possible patterns to call a web service from the CLR Stored procedure.
Thank you
---------------------------------------
The following is the error that is happening each
time I call the stored procedure
A .NET Framework error occurred during execution of
user defined routine or aggregate
'MyStoredProcedure':
System.Security.SecurityException: Request for the
permission of type 'System.Net.WebPermission, System,
Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check
(Object demand, StackCrawlMark& stackMark, Boolean
isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.HttpWebRequest..ctor(Uri uri,
ServicePoint servicePoint)
at System.Net.HttpRequestCreator.Create(Uri Uri)
at System.Net.WebRequest.Create(Uri requestUri,
Boolean useUriBase)
at System.Net.WebRequest.Create(Uri requestUri)
at
System.Web.Services.Protocols.WebClientProtocol.GetWe
bRequest(Uri uri)
at
System.Web.Services.Protocols.HttpWebClientProtocol.G
etWebRequest(Uri uri)
at
System.Web.Services.Protocols.SoapHttpClientProtocol.
GetWebRequest(Uri uri)
at
System.Web.Services.Protocols.SoapHttpClientProtocol.
Invoke(String methodName, Object[] parameters)
at MyWebServiceProxy.HelloWorld()
at MyStoredProcedure()