Inter-database Stored Procedures And Permissions
Jul 20, 2005
Hello all, this is my second post to this newsgroup. It's a question
about stored procedures and permissions and how these behave between
databases.
Here's the scenario. I have a database that stores information for a
system "A", and I have a different database on the same SQL server
that stores the login and other info "LOGIN". I write a stored
procedure in the "A" database that checks some tables in the "LOGIN"
database, let's call this "SP_A".
Additionally I have a user account that accesses all appropriate
stored procedures in "A" called "USER_A", and the same for the "LOGIN"
database, "USER_LOGIN".
Here's the part that raised my curiosity. I log into the server via
Query Analyzer using the "USER_A" account. I run "SP_A" which does a
join between some table in "A" and another table in "LOGIN". I give
"USER_A" execute permission on "SP_A", then I try to run "SP_A" and
get an error:
SELECT permission denied on object '(table in "LOGIN" database)',
database '(real name of "LOGIN")', owner 'dbo'
Huh? how come I need to assign additional select permissions in this
database if I'm not doing an actual select statement? I'm not even
dynamically running a select statement through an exec function. This
just struck me as odd, seeing as how I never explicitly set SELECT
permission on any table in "A" for "USER_A", yet my stored procedure
works, but between databases I have to assign extra permissions for a
stored procedure "SP_A" access to the tables in "LOGIN".
Anyone able to explain this behavior? Because I'm at a loss and I've
only been doing this DB thing for about 2 years.
Thanks in advance, all.
-TJ
View 4 Replies
ADVERTISEMENT
Aug 1, 2007
Good evening:
When assigning permissions to logins/roles, etc., does a login/user with rights to a stored procedure need rights to all of the tables and views that it accesses?
In other words:
If you create a login/user with rights to 3 stored procs, but deny access to the same user to the tables and/or views that the SP uses, will the sproc still run?
Stupid question? Sorry if it is.
** Future Daddy
View 11 Replies
View Related
Jun 12, 2001
Is there any way by which I can grant the same permissions to all of my stored procedures by one command?
Thanks,
Adita
View 2 Replies
View Related
Jul 3, 2001
Till yesterday I was able to execute the stored procedure sp_cycle_errorlog.
I am member of Domain Admin on NT.
I am member of sysadmin server roles on SQL Server 7.0 (SP1).
Now I receive the following error:
Server: Msg 15003, Level 16, State 1, Line 0
Only members of the sysadmin role can execute this stored procedure.
I have already stop and restart SQL Server.
Any idea?
Thank you.
View 1 Replies
View Related
Feb 14, 2007
i have a stored procedure that calls another stored procedure which does the following:
exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr < 0
begin
/* Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1) */
return
end
exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
now if I execute the first sp in management studio it doesn't error but if I call from asp it does error.
I gave the user permission to the first sp -- so I need to give permi
View 1 Replies
View Related
May 17, 2006
Using Server Management
Studio Express and SQL Server 2005 Express - is it possible to assign
Exec permissions for users on a sproc by sproc basis. If so, how do I
do this?
TIA
--
Mike Brind
View 1 Replies
View Related
Jul 4, 2013
I have an app which calls a SP, which in turn calls a CLR Stored Procedure.
The CLR stored procedure calls a number of different tables, using a Context connection string.
The issue is that the CLR SP requires the user to have permissions to the tables directly, instead of just permissions to the SP which was expected.
If I just give permission to the SP, then the CLR SP fails. So I then add the table permissions, and it then works.
So the question is, how do I raise security so the app does not have permissions on the tables?
View 5 Replies
View Related
Jan 25, 2008
I need to understand the permissions that a CLR stored procedure needs when it accesses tables. In a dbo TSQL stored procedure it has owner permissions on all dbo tables, so there is no need to grant permissions on tables to the database user.
Some developers recently implemented a CLR stored procedure that returned an error with update permission denied on table name. Once I granted the user account update permission on that table, it was able to execute OK.
I have been looking for a good explanation for the way the permissions to database objects need to be setup for CLR stored procedures. For example, could I have said to modify the procedure to use the EXECUTE AS clause, instead of granting the user account direct permission on the table? Does anyone have any links to good articles on this subject?
Edit:
I have a feeling I'm on my own trying to figure out how this works. I've been searching the web for hours, and I haven't found anything that directly addresses this.
CODO ERGO SUM
View 5 Replies
View Related
Jul 20, 2005
Hi, is there any way that I can automate granting user permissions totables/ stored procedures in SQL server 2000?I have a whole bunch of tables and rather than having to right click eachtable/ then permissions in Enterprise manager I would like to be able toiterate through each table object in a database and grant the relevantpermissions.... Same with stored procedures.Is this possible?? If so, how can I do itThanks in advanceMark
View 2 Replies
View Related
Apr 24, 2007
HI,
would like to know how to give execute permissions for all the stored procedures in a database at one shot. please advise.
View 6 Replies
View Related
Apr 25, 2005
I have a DataSet (Data Component in Beta 1) and I want to add Fill and
Get methods by using a Stored Procedure that was created by VS 2005 (aspnet_Membership_GetAllUsers). I probably need to use Enterprise Manager to do so but I am not sure what permissions I need to set and how to set them.
View 1 Replies
View Related
Jul 23, 2005
How can i add Execute permissions on the Stored Procedures under thecreated user permission iusing SQLDMO ?
View 1 Replies
View Related
Sep 20, 2007
Hey guys,I'm pretty new to SQL configuration, and I need to give EXECUTEpersmissions for one of the SQL user roles. I am running SQL 2005Management Studio Express - free version. I found the list of mystored procedures, but I can not locate any permissions screen. Cansomeone help point me in the right direction? Thanks!
View 7 Replies
View Related
Jul 23, 2007
All,
Here is my problem, its very simple, But I dont have a solution.
To run / import / what ever I else I forgot (?) SSIS in SQL SERVER, what are permissions I'll need.
So far I have developed everything in BIDS, when I try to migrate it to a sql server by using Import package in Integration Services I got the below error.
TITLE: Import Package
------------------------------
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)
------------------------------
ADDITIONAL INFORMATION:
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)
The error is very clear in itself, While I have raised a request for the execute permission of this stored procedure, i also like to know what kind of permissions I will need in MSDB to work with out any problems. So that I dont have to go to DBA for execute permission for each error I may get for this.Right now I dont have execute permission on any of the Stored Procs in MSDB.
If any body can show any pointers that would be help full.
View 4 Replies
View Related
Nov 16, 2006
Hi
My website uses GET variables a lot and i'm trying to safe guard as much as possible against SQL injection attacks. I'm trying to create permissions which will deny a user to Delete/Insert/Update various tables.
I have managed this with the tables themselves, but when using a stored procedure, the tables do not take into account the user permissions which were set for that table!
Basically, how do i stop a stored procedure from Deleting/Inserting/Updating tables? :(
many thanks
View 3 Replies
View Related
Aug 19, 2015
We 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 Related
Jun 25, 2015
What permission is required to run debug feature in SSMS(debug Stored Procedures). This is a development machine and developer requested for this.
EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.
EXECUTE permission was denied on object 'sp_sql_debug', database 'master'.
Is there any option other than giving sysadmin privilege on SQL?
View 0 Replies
View Related
May 8, 2008
Hi all,
I would like to enable users that do not belong to groups (server roles) such as sysadmin, serveradmin and don't have db permissions such as ddl_admin or db_owner to run some of the system stored procedures (such as sp_addumpdevice sp_configure sp_serveroption ...) and DBCC commands (such as DBCC CHECKFILEGROUP - requires ob_owner or sysadmin permission).
Is it possible to change permissions of SQL Server system stored procedures?
Is it possible to change permissions of SQL Server DBCC commands?
Thanks,
Assaf
View 1 Replies
View Related
Jul 20, 2005
HelloSuppose a database Db1 with tables tl1 and tl2 and a second database db2with tables tl3 et tl4.Is it possible to make a join between tables of the two databases ?As for example, Select * from tl1 INNER JOIN tl3 where tl1.Field1 =tl3.Field3Thank for any helpThierry
View 1 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Feb 24, 2005
I need to come up with a script that when executed it will create a stored procedure and trigger along with permissions. Is there a way to make this into a package. Any ideas?
View 3 Replies
View Related
Feb 13, 2008
Hi all,
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()
Me.GroupBox1.SuspendLayout()
Me.groupBox2.SuspendLayout()
CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'GroupBox1
'
Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices})
Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
Me.GroupBox1.Name = "GroupBox1"
Me.GroupBox1.Size = New System.Drawing.Size(240, 112)
Me.GroupBox1.TabIndex = 9
Me.GroupBox1.TabStop = False
'
'labelPAF
'
Me.labelPAF.Location = New System.Drawing.Point(8, 16)
Me.labelPAF.Name = "labelPAF"
Me.labelPAF.Size = New System.Drawing.Size(112, 32)
Me.labelPAF.TabIndex = 2
Me.labelPAF.Text = "Enter Price Adjustment Factor"
'
'labelNbrPrices
'
Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80)
Me.labelNbrPrices.Name = "labelNbrPrices"
Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16)
Me.labelNbrPrices.TabIndex = 5
'
'textBoxPAF
'
Me.textBoxPAF.Location = New System.Drawing.Point(120, 16)
Me.textBoxPAF.Name = "textBoxPAF"
Me.textBoxPAF.TabIndex = 0
Me.textBoxPAF.Text = ""
'
'UpdatePrices
'
Me.UpdatePrices.Location = New System.Drawing.Point(8, 48)
Me.UpdatePrices.Name = "UpdatePrices"
Me.UpdatePrices.Size = New System.Drawing.Size(88, 23)
Me.UpdatePrices.TabIndex = 6
Me.UpdatePrices.Text = "Update Prices"
'
'groupBox2
'
Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind})
<Part 1----To be continued due to the length of this post>
View 1 Replies
View Related
Jan 14, 2008
Hi all,
I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:
--User-defined stored procedure 'InsertCustomer'--
ALTER PROCEDURE dbo.InsertCustomer
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
INSERT INTO Customers
(
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
)
VALUES
(
@CustomerID,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@Phone,
@Fax
)
=================================================
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;" + _
"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@CustomerID", "PAULK")
command.Parameters.Add("@CompanyName", "Pauly's Bar")
command.Parameters.Add("@ContactName", "Paul Kimmel")
command.Parameters.Add("@ContactTitle", "The Fat Man")
command.Parameters.Add("@Address", "31025 La Jolla")
command.Parameters.Add("@City", "Inglewoog")
command.Parameters.Add("@Region", "CA")
command.Parameters.Add("@Counrty", "USA")
command.Parameters.Add("@PostalCode", "90425")
command.Parameters.Add("@Phone", "(415) 555-1234")
command.Parameters.Add("@Fax", "(415 555-1235")
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
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.
Thanks in advance,
Scott Chang
View 10 Replies
View Related
Jan 31, 2008
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
View 1 Replies
View Related
Apr 24, 2007
Hello everyone,I face currently a problem where I could need some input for searchingthe source of the ProblemSystem: SQL Server 9.0I fill from Database A with triggers Database B, everything worksfine.On Database B there is a Stored Procedures that checks the records andadd additional information accordingly, this Stored Procedures isnormally called by the application on "update and insert" in theaccording table.When I try to call this Stored Procedures from the Database A, thetrigger does not work anymore, even if I do a try catch over the wholetrigger, he never reach the Catch and the insert I try to do there toget the error message.On both Databases the user, that is taken to execute the trigger isexistent and DB-Owner of both Databases.If I go and execute the Stored Procedures manually after an insert orupdate to Database B everything works fine.I also already tried to check on Database B if there is an insert orupdate from Database A and if, to execute the Stored Procedures, withthe same result, nothing and all happens anymore, neither update onDatabase A and also not on Database B.And also I cant catch the error as the Try/Catch is not working.Hope I could explain it understandable and maybe someone remembersalready having the same problem.Thanks & Best regardsPascal
View 2 Replies
View Related
Apr 14, 2008
I've never dealt with stored procedures much.. but i have a new database
created.. imported the tables, but the stored procedures didnt get copied
over..
Is there an easy way to export them.. perhaps to a .sql file ... then import
them or run a script on the other database..
I have never done much with the query window before, so i'm not sure how to
handle this.. as there are around 20 stored procedures that need imported..
Thanks for any tips...
View 5 Replies
View Related
Mar 1, 2008
is there a way to copy whole stored procedures to another database?
I have aproximately 80 SPs in a database, I want to copy whole SPs into another DB. Is there a practical way to copy them?
Thanks
View 2 Replies
View Related
Jun 16, 2008
Hi,
Is there a practical way to transfer Stored Procedures to another database?
Thanks.
View 1 Replies
View Related
Apr 24, 2007
I am attempting to compile a list of questions that will enable meassess 'at risk' stored procedures that need to be remediated in orderto minimize unplanned downtime and enhance database performance. Isthere a subset of criteria (parameters) from the MS SQL Server BestPractices Analyzer that can be used for this purpose? Any pointers tothe appropriate documentation is most welcome.Thanks in advance.
View 1 Replies
View Related
Jul 20, 2005
I have 2 databases, one that we use called MyShop and one that I develop oncalled TestShop.After I have a stored procedure working the way I want in TestShop , isthere a way to just copy the SP to the other database without the copy andpaste method?. Same if I have a new table. Any way to add it in withoutrecreating it in the MyShop database?I am using Sql Server 2000ThanksAndy
View 1 Replies
View Related
Apr 19, 2007
How do i copy Stored Procedures from one SQL Express database to another?
View 8 Replies
View Related
Aug 31, 2006
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
View 2 Replies
View Related