VS.NET 2005 And CLR Stored Procedure Permission Sets.
Mar 24, 2006
VS.NET 2005 automatically deploys a CLR stored procedure when you start a project with debugging. However, if the CLR stored procedure attempts to access external resources you will get a message stating:
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I've isolated this to being because the CLR stored procedure does not have the EXTERNAL_ACCESS permission set.
Is there a security attribute that can be used to decorate the CLR stored procedure code so that VS.NET 2005 will register the CLR stored procedure with the correct permission set?
My stored procedure displays two result sets. How can i use that result sets in my 3-tier application. I want to bind first resultset to repeater control and second to label control. I am using SqlDataReader...
Since RDMBS and its language SQL is set-based would it make more senseto call a given stored process "Stored Sets" instead of currenttheorically misleading Stored Procedure, as a measure to prodprogrammers to think along the line of sets instead of procedure?
If you write a stored procedure to "SELECT MAX" from a table and the answer is null since the table is empty, how is the null answer returned to the program from the stored procedure? Do you actually get the letters "NULL" or do you just get a space? How should you define your variable in your program?
I have a stored procedure like the following. This returns 2 result sets, however i only want it to return 2nd (SELECT SomeField FROM SomeTable). How is this done? Note - it is not possible to change 'SomeSPThatReturnsAnIntAndAResultSet '
I have a SQL Server stored procedure that performs several queries and therefore returns several "result sets" at any one time.
Wiring it up via ADO.NET I populate a DataSet with a number of items in the Tables collection - which is great - and I can give each item a name for identification purposes once the DataSet is populated.
But I'd like to know if there is some way I can set the names of each result set *within the text of the stored procedure*, i.e. before the DataSet gets populated.
I have a novice question. How does one suppress result sets returned from a stored procedure?
I have created a procedure which makes use of multiple stored procedures . The purpose of this procedure (lets call it procA), is to count the rows returned from other procedures. The €œOther€? procedures will return rows having an unknown number of columns. I would like to limit any changes which may be needed to be made to the €œOther€? procs.
Once procA has collected all of the information (@@rowcount) from the inner procedures, then it will return a result set having several columns €“ mainly the subProcedure name and number of rows returned.
The purpose of procA is to query several subsystems and identify which ones need attention.
Cursor While Loop exec @ProcName @ObjectName,@userID,@syncDate set @recs = @@rowcount;
Hi there everyone. I have a stored procedure called “PagingTable� that I use for performing searches and specifying how many results to show per ‘page’ and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:
The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In ‘classic’ ASP I did this like this.
'Open the recordset rsItems.Open strSQL, conn, 0, 1
'Get the values required for drawing the paging table intCurrentPage = rsItems.Fields("CurrentPage").Value intTotalPages = rsItems.Fields("TotalPages").Value intTotalRows = rsItems.Fields("TotalRows").Value
'Advance to the next recordset Set rsItems = rsItems.NextRecordset
I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:
A) Bind the repeater control to the second resultset B) Build a “pager� of some sort using the values from the first resultset
I want to know how to handle multiple result sets return from Stored Procedure? I know one way is to insert the result sets into the table, but the limitation is the result sets must have the same data structure. If the result sets have different data structure, how can I handle it.
I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!
I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18 2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
com.microsoft.sqlserver.jdbc.SQLServer Exception: A server cursor cannot be opened on the given statement or statements . Use a default result set or client cursor.
If a SP doesnt have a temp table, then there is no issue, SP executes perfectly, but if a SP has a temp table, this error occurs.
SP :
create proc testProcedure @countrCode varchar(3) as
select countryname INTO #TMPCOU from country where countryCode = @countrCode SELECT COUNTRYNAME FROM #TMPCOU
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
one of the developer has a stored procedure that selects a data from one table and update to another table. i moved that stored procedure from development server to production server.
i gave that developer grant execute permission for that stored procedure. since that stored procedure selects and do update the tables, do i need to give update permission to that developer to that underlying tables also.
Hi, I have a schedule task which call one of my stored procedure, In this stored procedure, I need to change db owner of one of database, but I find sp_changedbowner do not allow me to specify db name,it only change current db,so I have to open a db before call sp_changedbowner,but it is invalid..
CREATE PROC demo as begin ...
use demo_db //it is invalid exec sp_changedbowner 'scott'
Which fixed database role allows a user to execute a user defined stored procedure while minimizing the amount of permissions given. I think db_Datareader will do the trick.
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?
Hello,I have a CLR stored procedure which send some values to an external URL by using the webclient, but for some reason I am getting this error.A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_LeadSend": 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.Net.WebClient.UploadValues can anyone please advice how to resolve this one...I am really having a hard luck....... thanks.
I'm trying to get a stored procedure working for a website on my local machine that uses ASP.NET 1.1 and MSDE. (I have a single instance of the latter installed, using Windows Authentication mode.)
I've been able to run SQL queries and such directly (using SqlCommand and so forth) by adding the proper reader role to the account MACHINENAMEASPNET. (Substituting my actual machine name for MACHINENAME, of course.) However, when I try to run a stored procedure from an .aspx page, I get the following error:
I've researched this problem here and other places, and every time I get to a response that says to grant execute permission (via OSQL -E) with the following statements:
use mydbasename go grant execute on MySPName to MACHINENAMEASPNET go
(There are sometimes some other intervening statements to add ASPNET as a user account, but when I use those I'm told that the account already exists ... I had added it previously via the Web Data Administrator in order to get reader permissions for SELECT statements and so forth.)
My problem is that the GRANT EXECUTE statement always fails with the following error:
Line 1: Incorrect syntax near ''
Using a forward slash instead doesn't make any difference. If I put single quotes around 'MACHINENAMEASPNET', then the error changes to:
Line 1: Incorrect syntax near 'MACHINENAMEASPNET'
And if I eliminate the machine name, then the error is:
Msg 4604, Level 16, State 1, Server MACHINENAME, Line 1 There is no such user or group 'ASPNET'
So can someone please let me know what I am missing that doesn't allow the GRANT EXECUTE to work?
Here is the stack trace (note that I have altered some names and paths for purposes of security):
Suddenly a stored procedure, very much like several others, is givingEXECUTE permission denied on object 'Add_Adjustment', database'InStab', owner 'zhoskin'.server:Msg 229, Level 14, State 5, Procedure Add_Adjustment, Line 18.I'm zhoskin. I am the dbo and created the procedure, and when I lookat its properties, I have EXEC permission. Line 18 is just the returnstatement. The values are all appropriate for the table. So what isusually going on when a stored procedure denies access to its owner?Thanks//Zeke HoskinCREATE Procedure Add_Adjustment (@AdjAcc Int, @AdjType Char, @AdjAmtMoney, @AdjYrMth Int, @AdjDate Datetime)/* Add a Dep Adj (Type Z, Negative) or WD Adj (type Y, Positive) */ASIF @AdjType = 'Z'BEGINInsert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,TxnDate)VALUES(@AdjAcc, @AdjType, @AdjAmt, -1, @AdjYrMth, @AdjDate)ENDIF @AdjType = 'Y'BEGINInsert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,TxnDate)VALUES(@AdjAcc, @AdjType, @AdjAmt, 1, @AdjYrMth, @AdjDate)/*this is just to afect line numbers*/END/* set nocount on *//*space holder*/return/*more space*/GO
I develop a database that notifies clients when data changes by sending an UDP broadcast message using an extended stored procedure. Now I want to use a CLR stored procedure to send the UDP broadcast instead:
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net.Sockets;
public partial class UserDefinedFunctions
{
[SqlProcedure]
public static void UdpSend(SqlString address, SqlInt32 port, SqlString message)
{
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
I have found that to be allowed to send to 255.255.255.255 I must give the assembly permission set 'Unsafe'. If I change to 'External access' I get:
Msg 6522, Level 16, State 1, Procedure UdpSend, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UdpSend':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, 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.Sockets.Socket.CheckSetOptionPermissions(SocketOptionLevel optionLevel, SocketOptionName optionName)
at System.Net.Sockets.UdpClient.CheckForBroadcast(IPAddress ipAddress)
at System.Net.Sockets.UdpClient.Send(Byte[] dgram, Int32 bytes, String hostname, Int32 port)
at UserDefinedFunctions.UdpSend(SqlString address, SqlInt32 port, SqlString message)
I cannot use permission set 'Unsafe' in production environment, so what I want is to customize the effective permissions with higher resoloution than the three pre-defined permission sets 'Safe', 'External access' and 'Unsafe'. Except from what is allowed by 'Safe' I only want the permissions necessary to send an UDP broadcast.
PLEASE PLEASE PLEASE...... I did not get a single response for the last 6 hours... And during this time I was searching and trying to understand the problem but I am really stuck. If this is the wrong forum to ask this question, please redirect me. Really begging for replies...[:'(] If I use the custom SQL statements in SqlDataSource, the application runs fine within the development environment (VS2005) but errors out if I publish the web site and access outside of the environment. In order to find-out the problem, I made the following test: I created a select statement in one SqlDataSource to fill-in a GridView. I used the exact same statement to create a stored procedure and used that SP in second SqlDataSource and I fill a second GridView. When I debug or run the application, both grids are filled OK and everything works fine. However, when I publish the web site and try to do same only the stored procedure works fine and when I try to fill the grid using the built-in SQL, the page gives error. The error mesage is as follows when I use the address 'localhost': Server Error in '/' Application.
The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'. 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.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322.......da da da ....... If I access the page using the IP address the message chages to below but it is not the issue, I just give it if it helps to find the problem: Server Error in '/' Application.
Runtime Error Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off". My SqlDataSource s are like this: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HomerConnectionString %>" SelectCommand="TestRemoteAccess" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="Param1" PropertyName="Text" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:HomerConnectionString %>" SelectCommand="SELECT FirstName, LastName, Business FROM Contacts WHERE (ContactID = @Param1)"> <SelectParameters> <asp:ControlParameter ControlID="TextBox2" Name="Param1" PropertyName="Text" /> </SelectParameters> </asp:SqlDataSource>
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
Dear All,This is a query surrounding a problem I encounteredyesterday.In SQL Server, it is possible to write a procedure thathas one or more select statements in it.The results from these select statements will all beindividually returned to SQL Query Analyser where theycan be viewed in "grid" views. Also, these individualresults sets can be consumed by eg ADO.NET by steppingthrough each results set in turn and processing therespective results.My question is, can you do the same in a SQL Serverprocedure? ie:Create Procedure Proc1ASbeginselect Col1, COl2from Table1select Col1, Col2, Col3from Table2endCreate Procedure Proc2ASbeginexec Proc1endCan both/either of the results sets from Proc1 beconsumed by the calling procedure Proc2?I can see that you could design the procedures up-front to do almost anything without consuming theresult sets in this way, but if the proceduresreturning the results sets are already built andin use in other places (for instance in client code),can they be re-used on server-side SQL procedures?Thanks in anticipation!Paul.
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....
Hi, I want to get data from Oracle 10g Stored procedure to Reporting Services 2005. I could pass a SQL text and get a record set, but I want to execute a store proc and get the record set.
1. Add New Data Source 2. Choose Type : Oracle and connection tested OK 3. { call Test_Package.Test_Procedure(?) } is it wrong... how to write??? There is an error in the query. ORA-00911: invalid character
Also make sure that you use the text-based generic query designer (2 panes !) instead of the visual query designer (4 panes) - you can switch between them through an icon on the toolbar in the data view of report designer. Question : I tried many methods but unable to solve it...
create or replace PACKAGE Test_Package AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE; END Test_Package;
create or replace PROCEDURE Test_Procedure ( Test_Cursor IN OUT Test_Package.Test_Type, Test_Parameter IN Test_Table.ID%TYPE ) AS BEGIN OPEN Test_Cursor FOR SELECT * FROM Test_Table WHERE Test_Table.ID >= Test_Parameter; END Test_Procedure;
The below site gave some example but i could not solve it... any suggestions greatly appreciated...
Hello, I am using existing code, which I am trying to convert from using MS Access to SQL Server 2005... The data set works fine with MS Access database, however when executing with SQL Server 2005 as data source, it generates the following error: "..The data types ntext and nvarchar are incompatible in the equal to operator..." in this line: count = adapter.Update(dataset); Not sure what should I look for since data sets are new to me.. Where should I check to fix this problem? I have noticed that the table has two columns with nvarchar...
we have a application written in straight ASP, we recently upgraded our SQL database connecting to the application from SQL 2000 to 2005 here is the issue
currently SQL 2005 is located in our test and UAT environments, SQL 2005 is not clustered in either location. Now let me make it clear THIS PROBLEM IS NOT HAPPENING IN OUR TEST ENVIRONMENT, I REPEAT THIS PROBLEM IS NOT HAPPENING IN OUR TEST ENVIROMENT, THIS PROBLEM IS ONLY HAPPENING IN OUR UAT ENVIROMENT
Each enviroment is outfitted with the following SQL 2005 environment, Enterprise Edition SP1 with Cumlative hotfix package 2153 just the SISS fix ONLY
now the problem is this, we have a vb program located directly on the server that has SQL 2005 on it that VB program inserts a timestamp into a table inside the database, now how this happens is the program connects to the database and opens a recordset with a forwardonly cursor and inserts the timestamp
NOW THE EXACT PROBLEM IS THAT THE VB PROGRAM CANNOT OPEN MULTIPLE RECORDSETS, THE ERROR RECEIVED IS LABLED LIKE THIS
NOW here is the interesting part, AGAIN THIS IS NOT HAPPENING IN OUR TEST ENVIRONMENT, THE VB PROGRAM AGAIN IS ON THE TEST SQL SERVER AND HAS A FORWARD ONLY CURSOR AND INSERTS JUST FINE
NOW IF I MOVE THE VB PROGRAM OUT OF OUR UAT ENVIRONMENT ONTO ANOTHER SERVER IT INSERTS THE RECORD ONTO SQL SERVER JUST FINE, IT OPENS THE RECORDSET AND OPERATES NORMALLY, BOTH SERVERS HAVE EXACT SAME PERMISSIONS
Can anyone help me understand whats happening here
One note, when we changed the cursortype from forward-only to keyset it worked fine but that doesnt explain things
why does it work in test so clearly what we did with changing the cursortype isnt the answer
I have a very strange problem using SQL Server 2005
I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:
Select * from custoemr where EmployeeID = 3
on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..
Can someome please teel me how to preceed in resolving this issue..
How to copy only some stored procedure from dev server into prod server in sql server 2005? Should I use import/export wizard or copy database wizard? Thanks in advance.
Hi,I'd like to know if anyone has had experience upgrading their storedprocedures directly from 7 over to 2005, and what issues they've runinto.Also, if there are any articles available on this subject, couldsomeone please point me in the right direction?Thanks!!
