Database Access Via COM Objects - V- Database Access Via Stored Procedures
Aug 17, 2000
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?
I have read that it is possible to configure sql server express so that the database can only be accessed through stored procedures. Can anyone tell me how to do this. Many thanks.martin
I have a database (SQL 2005) with two schemas (dbo and s1) and with tables defined in dbo and tables defined in s1. The stored procedures are also defined in both schemas, some of them in dbo some of them in s1. Some of the stored procedures query tables from dbo and s1 at the same time. I want to have a new db role with access to the database only through sps and no other access read/write to the tables. I created a new db role and granted execute permission to it and assigned a user to it. When I execute stored procedure defined in dbo with query against dbo tables, it works as expected. However, if I run stored procedure defined in s1 with query to table in dbo, I receive error about missing select permission for the table in dbo. I am not sure why, but I can assume there is an issue with the ownership chain. I can grand read/write permission for the tables, but this will break our original requirement for limited access to the db only through sp. The other option is to have another role r2, with read/write, privilege and to use EXECUTE AS r2 in the sp.
I would like to ask first why the error for missing select permission happens and is there another way to have role restricted to only execute permission for all stored procedures.
We are running SQL Server 2012 on Windows 2008 Server. In one database, we would like to create a view which access objects in another database without giving the user permissions to the underlying base tables in the other database. The ownership chain is broken in this case. Can this be accomplished (considering the ownership chain is broken)? If so, what is the easiest method to accomplish this task? Or
Example 1 (Works):
In DB1: --UserA selects from Schema1.View1 (which access tables in DB2).
In DB2: --UserA exists with select permissions on the base tables accessed by Schema1.View1 (in DB1).
Example 2 (trying to accomplish):
In DB1: --UserA selects from Schema1.View1 (which access tables in DB2).
In DB2: --UserA exists (or may not exists) with NO permissions on the base tables accessed by Schema1.View1 (in DB1).
Hi. I'm trying to write a stored procedure that will access another database on a different server. How can I set this up in a stored procedure? It is a SQL Server 2000 database and it will be using SQL server authentication. Thanks!
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net.
My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList').
I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures.
Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo.
Example of stored procedure on external database:
/****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/ CREATE PROCEDURE dbo.AddUser ( @Username Varchar(20), @Password Varchar(20) ) AS ( Select User_ID From UserList Where User_Username = @Username ) GO
Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops!
Any help would be greatly appreciated as I am completely stuck.
I developed a database with Access 2003 and everything was working good until my tech came in and reformated my hard drive and install a new Ghost image that met our company standards.
Now I cannot go in and make any changes to any of the tables, queries and forms. All of this started when a new Ghost image was installed on my pc.
The message I get when I try to open my database is "You do not have permission to run "tblSwitchboard." I get the same error message when I try to do anything at all on the database.
How can I remove access to extended (xp_) stored procedures? Is there any revoke on <stored_procedure_name> ... command? How can I generate a script of all users who have execute privileges for these procedures? Also, is there any way of restricting (instead of removing) access to those procedures?
Any help will be greatly appreciated!!! Thanks, Alla
I'm going through the SQLSecurity Checklist I found at sqlsecurity.com. One of the points it says to "Restrict to sysadmins-only access to stored procedures and extended stored procedures that you believe could pose a threat." It also lists a bunch of stored procs and extended stored procs that you should consider restricting to sysadmins only. I was wondering if someone could give me some pointers on how to do this? I would like to write a script that I could run on every sql server 2000 install that would do this. How could I ensure that every user does not have access except the sysadmins?
I am working on a corporate project. I have many stored procedures(>100) and wanted to know which applications( or Frontends) in the network use or consume my stored procedures. How can i do that in SQL 2005??
Hi. I have set up an instance of SQL 2005 on my local computer. I use MS SQL Server Management Studio to login into my 2005 instance via Windows login. When I expand "Stored Procedures" under "Programmability" of the Database I want and go to a stored procedure and right click, I don't see the "properities" selection.
I know it's there because I can get to it on other instances but I can't get to it on my local instance. I added my windows user to my Logins and then to my database users but it didn't help.
Can anyone please tell me what I need to do so I can access the properties of the stored procedures?
I have a design problem which I am hoping somebody can shed some light on.
I am running SQL Server 2000 using SQL authenticaiton (due to be changed to Windows authentication in the next 6 months). I have a table in my database which we shall call monthly. I want to restrict the ability to insert to the monthly table to 2 stored procedures (proc_abc & proc_xy) which I have written which do various other validation checks before it inserts the data into monthly.
Users with the Foo function assigned are able to execute proc_abc & proc_xy
I have written a VB application which can be used by users who are not familiar with SQL to be able to execute these stored procedures. (Must have Foo function in order to login to this application).
I want to restrict the ability to execute the stored procedures to users using the VB application only, and thus not be able to execute the stored procedure using Query Analyzer or such like for any Foo user.
Is there anyway I can do this?
One suggestion put to me is two split the functions. Have one function lets call it Top which can access the VB application and then have another function called Bottom which is able to execute the stored procedures. Only the VB app would have access to the Bottom credentials. But is this secure? Would I just hard code the credentials for the Bottom function user within the VB app? This doesn's seem a secure way of doing things to me.
Can Microsoft Access access a stored procedure on the SQL Server? If not, is there any way to assign a parameter to a view? I can link to a view in Access, but I cannot link to a procedure. I need to SELECT * from aTable WHERE ID = [@PassidInID]. Any way to do this through Access? The reason for this, is because I am trying to run an existing query (which is very busy - using other queries with queries in them etc.), and I keep getting an ODBC error. I am thinking that if I move the queries to the SQL Server, it may get rid of this error? It's a 2-tier app I have. Tables on the server, forms, queries, and everything else in Access. Thanks in advance - I hope.
Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _ ByVal strCompany As String, _ ByVal decSerial As Decimal, _ ByVal strSerialCode As String, _ ByVal strSerialScan As String, _ ByVal decMasterSerialNumber As Decimal, _ ByVal strCustomerPart As String, _ ByVal strTakataPart As String, _ ByVal strCustomerRanNo As String, _ ByVal strCustomerAbv As String, _ ByVal strDestinationAbv As String, _ ByVal decQty As Decimal, _ ByVal strCreatDate As String, _ ByVal decVoidSerialNo As Decimal, _ ByVal strProductionLineNo As String, _ ByVal strProcType As String)
Dim sp As SqlPipe = SqlContext.Pipe Dim strResult As Integer = 0 Dim strErrorText As String = String.Empty Dim dsData As New DataSet Dim parameter(15) As OleDbParameter If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then ' Populate parameter collection
If dsData.Tables.Count > 0 Then dsData.Tables(0).TableName = "Supreeth" Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString() Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString()
' I am not sure here SqlContext.Pipe.Send(bitresult) SqlContext.Pipe.Send("No errors")
End If
Else Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid") End If
End Sub
Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet) '********************************************* ' Declare Variables '********************************************* Dim daAdaptor As OleDbDataAdapter Dim cmdAS400 As OleDbCommand 'Dim dstestMe As New DataSet Try cmdAS400 = CreateCommand(strProcedure, parms) daAdaptor = New OleDbDataAdapter(cmdAS400)
' Fill the Data Set daAdaptor.Fill(dsData) Catch expError As OleDbException daAdaptor = Nothing Finally daAdaptor = Nothing cmdAS400.Dispose() 'Me.Close()
End Try
End Sub Public Shared Function CreateParameter(ByVal name As String, _ ByVal type As OleDbType, _ ByVal size As Integer, _ ByVal direction As ParameterDirection, _ ByVal paramValue As Object) As OleDbParameter Dim param As OleDbParameter = New OleDbParameter param.ParameterName = name param.OleDbType = type param.Size = size param.Direction = direction param.Value = paramValue Return param End Function
Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand Dim CmdSAS400 As OleDbCommand Dim parameter As OleDbParameter Dim connAS400 As OleDbConnection connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;") connAS400.Open()
CmdSAS400 = connAS400.CreateCommand() CmdSAS400.CommandText = strProcedure CmdSAS400.CommandType = CommandType.StoredProcedure CmdSAS400.Parameters.Clear() 'CmdAS400.CommandTimeout = intTimeOut If (prams Is Nothing) Then Else For Each parameter In prams CmdSAS400.Parameters.Add(parameter) Next
End If
Return CmdSAS400
End Function
I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset. My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs
Hi there, I have a database table with a field to specify the username of records that particular user is allowed to access (read only). I want to be certain the user does not have access to any other records. So I'm using a stored procedure to query the table with "WHERE dbuser = USER". My problem is that the stored procedure is returning an error that the table I'm querying cannot be found, and I suspect it is because the user does not have proper permissions to see those tables, even through the stored procedure. So my question is, how do I grant the user sufficient access for this stored procedure to work without outright granting them select permission on the tables? Much appreciated!
I have upsized a complex Access 97 application to SQL Server 7, and want to substantially preserve the user interface design by using an Access 2000 project (*.adp) as the front end.
Most of the original Access queries accepted their parameters from screen form objects, and this works well using SQL 7 stored procedures as form recordsources.
However, Access 2000 does not seem to provide a way to tell a listbox or combo box how to pass a parameter value from a screen form object to its SQL 7 stored procedure rowsource.
These parametized listboxes are critical to the interface design. Please help!!!
I am used to working in Access and just recently became somewhate proficient using custom functions in modules. I am trying to figure out what the equivalent of functions is in SQL Server. I mean, does a Stored Procedure in SQL Server replace a module in Access? Can you declare different functions in SQL Server like you can in Access? Thanks for your help. Mike
Hi, Can someone help me with this problem. I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:
Dim cn, cmd Set cn = CreateObject("ADODB.Connection") cn.Open "SQL" //Data Source Name Set cmd = CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn cmd.CommandText = "LoadApplicants" //Stored Procedure Name cmd.CommandType = adCmdStoredProc cmd.Execute
for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?
I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko
In our company, after switching to Office 2007, Access 2007 runtime is installed on our general user machines. Everything is just fine and beautiful, except that there is one issue with runtime:
Several forms in my Access Project use stored procedures in SQL Server 2000 as their record source. When i try to open the forms in Access 2007 Runtime on user machines, i receive an error that "The record source dbo.Myprocedure speified on this form or report does not exist ". I use dbo.ProcName format and the forms open correctly in my full version Access. What could be the problem?
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
) ================================================= In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code: --Form_Kimmel.vb-- Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
End Class ==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Hi I have an application that I have started to develop. I have successfully set the connection to open the SQL Server database that I created. When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process. At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again. I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger
