I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?
Thanks for tips
Hello All,I tried to set the access permissions for debugging stored procedure by reading the articlehttp://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspxandhttp://technet.microsoft.com/en-us/library/ms164014.aspxI have tried to add the role to sysaminas follows1)SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_sdidebug'(to find the sp)Error:--The stored procedure not found2)sp_addsrvrolemember 'Developmentswati.jain', 'sysadmin' though this is executed successfuly . Error is still persisting
Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have permission to execute master.sp_sdidebug.
Hi, Does anyone has a script to get user permissions on all tables?
Thanks
I have the following sp..REATE Procedure SSSP_VehicleReg_Add @DECAL_NO varchar (9) , @NAME varchar (26) , @SSN varchar (9) , @DLN varchar (10) , @Address_1 varchar , @Address_2 varchar , @City varchar (50) , @State_Code varchar , @Zip_Code varchar (9) , @Country_Code varchar (4) , @PHONE varchar (12) , @VM varchar (10) , @MODEL varchar (10) , @VLPNUMBER varchar (10) , @VLPSTATE varchar (2) , @Date_Iss datetime, @Date_Exp datetime, @tran_Date datetime, @Op_Code varchar (20) , @ReturnStatus integer output, @ReturnMessage Varchar (50) output /* Adds a record to the Vehicle Registration table.*/ AS If @Decal_no = ' ' Begin Set @ReturnStatus = 0 Set @ReturnMessage = 'Record not added Key Fields are not valid' End Else If dbo.VehicleReg_Check (@Decal_No) = 0 Begin Insert INTO VehicleReg (Decal_NO, Name, SSN, DLN, Address_1, Address_2, City, State_Code, Zip_Code, Country_Code, Phone, VM, Model, VLPNUMBER, VLPSTATE, Date_Iss, Date_Exp, Tran_Date, Op_Code) Values (@Decal_NO, @Name, @SSN, @DLN, @Address_1, @Address_2, @City, @State_Code, @Zip_Code, @Country_Code, @Phone, @VM, @Model, @VLPNUMBER, @VLPSTATE, @Date_Iss, @Date_Exp, @Tran_Date, @Op_Code) Set @ReturnStatus = 1 Set @ReturnMessage = 'Record added' End Else Begin Set @ReturnStatus = 0 Set @ReturnMessage = 'Record not added' EndGO That is being called via...Dim SqlCmd As New SqlCommand("SSSP_VehicleReg_ADD", MYGF.cnMydb)Dim Current_Time As String = CStr(Now.Date + Now.TimeOfDay)SqlCmd.CommandType = CommandType.StoredProcedureSqlCmd.Parameters.Add("@Decal_No", SqlDbType.VarChar, 9, txtDecal.Text)SqlCmd.Parameters.Add("@NAME", SqlDbType.VarChar, 26, txtName.Text)SqlCmd.Parameters.Add("@SSN", SqlDbType.VarChar, 9, txtStuid.Text)SqlCmd.Parameters.Add("@DLN", SqlDbType.VarChar, 10, txtDln.Text)SqlCmd.Parameters.Add("@Address_1", SqlDbType.VarChar, 50, txtAddress_1.Text)SqlCmd.Parameters.Add("@Address_2", SqlDbType.VarChar, 50, txtAddress_2.Text)SqlCmd.Parameters.Add("@City", SqlDbType.VarChar, 50, txtCity.Text)SqlCmd.Parameters.Add("@State_Code", SqlDbType.VarChar, 2, ddlState.Text)SqlCmd.Parameters.Add("@Zip_Code", SqlDbType.VarChar, 9, txtZip.Text)SqlCmd.Parameters.Add("@Country_Code", SqlDbType.VarChar, 4, " ")SqlCmd.Parameters.Add("@PHONE", SqlDbType.VarChar, 12, txtPhone.Text)SqlCmd.Parameters.Add("@VM", SqlDbType.VarChar, 10, txtMake.Text)SqlCmd.Parameters.Add("@MODEL", SqlDbType.VarChar, 10, txtModel.Text)SqlCmd.Parameters.Add("@VLPNUMBER", SqlDbType.VarChar, 10, txtTagNo.Text)SqlCmd.Parameters.Add("@VLPSTATE", SqlDbType.VarChar, 2, " ")SqlCmd.Parameters.Add("@Date_Iss", SqlDbType.DateTime, 8, txtIssDate.Text)SqlCmd.Parameters.Add("@Date_Exp", SqlDbType.DateTime, 8, txtExpDate.Text)SqlCmd.Parameters.Add("@tran_Date", SqlDbType.DateTime, 8, Current_Time)SqlCmd.Parameters.Add("@Op_Code", SqlDbType.VarChar, 20, "xxxx")Dim ReturnStatus As New SqlParameter(("@ReturnStatus"), SqlDbType.Int, 1, ParameterDirection.Output)Dim ReturnMessage As New SqlParameter(("@ReturnMessage"), SqlDbType.VarChar, 50, ParameterDirection.Output)SqlCmd.Parameters.Add(ReturnStatus)SqlCmd.Parameters.Add(ReturnMessage)SqlCmd.ExecuteNonQuery()Which fails with the following error...System.Data.SqlClient.SqlException was unhandled by user code Class=16 ErrorCode=-2146232060 LineNumber=0 Message="Procedure 'SSSP_VehicleReg_Add' expects parameter '@DECAL_NO', which was not supplied." Number=201 Procedure="SSSP_VehicleReg_Add" Server= Source=".Net SqlClient Data Provider" State=6 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Vechicle_Registration.Move_to_database() in C:InetpubwwwrootssccintranetVechicle_Registration.aspx.vb:line 304 at Vechicle_Registration.BtnSave_Click(Object sender, EventArgs e) in C:InetpubwwwrootssccintranetVechicle_Registration.aspx.vb:line 34 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Any help would be appreciated
View 2 Replies View RelatedI changed "@Decal_No" to "@Decal_NO" same error
View 2 Replies View RelatedHello,
I know that syscomments stores the stored procedures in chunks of 4000 characters. Some of my Stored proc's are >4000 characters.I use a temporary table to stock them after some modifications made to them.How can I execute them one by one, do i need to concatenate one whole stored proc's text in one single line(if yes, how can that be done?) Can variables be dynamically created, assigned values and then destroyed? Please help.
Hi All,I am running SQL2000. Can anyone tell me how I can use the contents ofa table as parameters for a stored procedure that resides on adifferent SQL server?I have a table:Customers:Cust_IDCust_NameCust_ContactCust_PhoneI need to execute a stored procedure and pass each of the above asparameters:@CustID, @CustName, @CustContact,@CustPhoneThe stored procedure also returns an error code.Thanks,Danny
View 3 Replies View RelatedI have a table with a list of stored procedures, I iterate using a Foreach loop which i iterate through an ADO.NET recordset that I created by executing a select SQL statement.
Now my problem is that in the foreach loop i have an execute sql task container which basically just executes these stored procedures using the 'exec ?' statement (i grab the name of each procedure into a variable). However, error trapping seems to be very difficult. I just figured out how to get a ReturnCode from the execute statement, but apparently I have to use it outside the Execute SQL task ....prolly use an IF condition to see if its not 0....but how do I go about doing this?
Also, I want to error trap if a stored procedure doesnt execute because of some error, how could i possibly handle an error like that? I dont want the foreach iteration to stop on an error because a stored procedure wasnt called either, but currently it does stop the entire package.
Thoughts anyone?
I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?
View 1 Replies View RelatedHello SQL Experts,
we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.
A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.
The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.
In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?
The Owner of sp and table is dbo.
Thanks for your replies!
Hey guys,
I have found out that we can execute multiple queries and receive multiple resultsets in a SqlDataReader by executing the queries with ";" separators,
However, what if we wanted to execute two sqlcommand storedprocedures? are there any other way rather than placing "Execute sp1;Execute sp2" in the command text?
I would like to do it in a way whereby I can pass in two storedprocedures with parameters binding capability rather than execute sp1(param1, param2);execute sp2(param1, param2, param3)
Hope to get some suggestions and advice from you guys,
Thank you very much in advance.
Hi
I have a database created on SQL Server 2000 server. And I have created a new database with SQL Server2005. I want to extract some data from old SQL Server2000 database. I have written a SP at SQL Server 2000 database and I want to know how to execute a SQL Server2000 SP from SQL Server2005.
Thanks & Regards!
I would be grateful if someone can provide some kind of explanation over why this issue has happened.
I had the following statements in a query window.
exec dbo.usp_sProc1
go
exec dbo.usp_sProc2
go
exec dbo.usp_sProc3
go
In order to monitor the progress of each procedure, I have a table called progress_log into which each step executing in the procedure is written. Each procedure has a "Begin" and "End" text written to this progress_log.
When I clicked the execution button on the window, I made the assumption that each stored procedure will finish before the next one executes. However something different happened.
The progress_log shows the following
S/N proc_Name Step
1 sProc1 Begin
2 sProc1 sProctep1
3 sProc1 sproc1: Step2
4 sproc1 sproc1tep3
5 sProc2 sproc2:Begin
6 sProc2 sProc2tep1
7 sproc2 sProc2tep2
8 sproc2 sproc2:End
9 sProc3 Begin
10 sProc3 Sproc3: Step1
From the above (S/N: 5) , it can be seen that the procedure did not finish executing Sproc1 because there was no "END" statement written to the Progress_Log before the "Begin" statement of SProc2.
On checking the error_log, I found there were no errors in the stored procedure. However, a message was written to the query window about insufficient space on the database at S/N:10 in the log above.
How is it possible that the Sproc2 stored procedure can be started without the Sproc1 stored procedure having ended?
I can give more explanation if not clear.
many thanks for any help
Hi
I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2.
For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure.
In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user.
Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this.
If anyone can point me in the right direction on this...
I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005?
Thanking you in advance
I have a user on my database that has the following base permissions :
public
db_datareader
I need to give this user permission to edit a single stored procedure. I have tried using the following command :
GRANT ALL ON stored_procedure_name TO username
Which executes successfully, but the user still cannot edit the stored procedure.
If I give the user db_ddladmin permission they can edit all the user stored procedures, but for security reasons I would prefer to be able to this this at procedure level rather than a global permission on all user procs.
Does anybody know how I can do this?
EDIT : This is on SQL 2000
finding all users that have permission to modify stored procedures in SQL SERVER.
View 0 Replies View RelatedOn our production SQL 2005 servers I want to give developers readonly access to each user database and also give them the ability to see stored procedures. Readonly is handled through db_datareader, but how do I give them the ability to see stored procedures without granting permission to execute them?
Thanks, Dave
I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE
-----
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);
MS SQL SERVER STORED PROCEDURE:
-------------------------------
CREATE PROCEDURE nextval
@sequence varchar(100)AS
BEGIN
-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @sequence_id int
set @sequence_id = -1
UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence
RETURN @sequence_id
END
If I have several stored procedures, all of them making inserts/updates to the same tables, what is the safest way to run them in sequence?
The context is an asp.net application; I have an aspx page where a click on a button (or more) will launch stored procedures in execution. I have encountered the unfortunate situation before, when stored proc #2 started long before stored proc #1 finished and this caused problems.
If I group all stroed procs in one that simply calls all of them in the needed sequence:
exec stproc1
exec stproc2
.....
exec stprocn
Will they execute in a single thread? Is there any guarantee that stproc2 will be executed when stproc1 finishes, and so on?
Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.
|_N_T_|
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'
I have about 30 different reports that I want to pull into a dashboard. I need to make sure that they don't execute in serial to get good performance.
There are two ways I can approach it
1) I can create a stored procedure for each report and then make sync calls for each of the reports from the web site. So, basically this will be controlled from the web end.
2) I can do this from the SQL Server database, if there is someway to execute these stored procedures in parallel.
I'm a new developer to both SQL Server 2005 & Windows 2003, so forgive me if this question seems a little too basic. I'm coming from a Oracle and UNIX background.
I've create a stored procedure in SQL Server 2005. I now want to execute this from the command line in Windows 2003. Eventually, I want our UNIX scheduler, autosys (which runs on a different UNIX machine obviously) to be able to execute this. In my old environment, I created a UNIX shell script as a wrapper let's say 123.sh. This shell script would accept as a parameter the name of the stored procedure I wanted to execute. If this stored procedure also had parameters it needed to be passed to it, I would have strung these values out in the command line in UNIX. Two examples of how the command line in UNIX I used to execute the Oracle stored procedure might look are listed below.
123.sh sp_my_stored_procedure input_parm1 input_parm2
123.sh sp_different_stored_procedure input_parm1
This way anytime I created a new stored procedure, I could reuse the shell script wrapper 123.sh and just pass in the name of the newly created stored procedure and any parameters it needed.
How can I accomplish this same type of functionality in the SQL Server 2005/Windows 2003 environment.
Thanks, Jim
How do I get a list of the stored procedures currently in the database? Is there a quick way to edit them?
Thank you!
I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.
@Cycle sql_variant = 2
WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication
AS
BEGIN
SET NOCOUNT ON;
declare @execution_id bigint
[code]....
When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.
Hi
This is kind of a visual studio question, but pertinent to db' s - due to many changes of our application I know that there are many stored procs in the database that are no longer used. Since I cannot get a list from the programmers, is there any way to get a list out of visual studio, of every SQL stored procedure called in that project? (Going through each form manually will just be too time consuming for me or the programmers)
Anyone know any tricks??
thx
Des
Is there any oen can tell me how i can run some system sp or exec something to list all tables or stored procedures in a database, like i use select statement to list some coulmn data? thanks in advance.
View 3 Replies View RelatedI have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.
[code=sql]
CREATE PROCEDURE [dbo].[__usp_DatabaseFieldsize_Get]
@pTablename nvarchar(128)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] ....
If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.
Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?
If so, how to continue.
In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not work. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.
Just wondering if anyone knows of a useful command to assign execute permissions to a batch of stored procs to a user/role. I've got too many stored procs to manually go thru the steps of browsing for them and scrolling thru each one and clicking "execute" for each one.
Also, would like to know if its possible to update a batch of stored procs that begin with a prefix like "spSomething_".
Any info would be helpful! TIA.
Is there a built in stored procedure that would allow me to list outthe database permissions assigned to a particular user or role?
View 2 Replies View Relatedis there a sql query i can type in to list all the user stored procedures for the tables within my database?
or do i have to find them manually and how?
Dear all,
How to find out how many user stored procedures are there in a single Database in sql server.
Thanks,
msrs
Hi There,
I've written an inline table-valued function in SQL such as the following:
ALTER FUNCTION dbo.GetCityByID( @CityID int)
RETURNS TABLE
AS
RETURN(
SELECT
Name,
Url
FROM Cities
WHERE (CityID = @CityID) )
suppose that Cities table includes three fields (CityID, Name, Url).
By the way I wrote a store procedure as follow:
ALTER PROCEDURE MyProcedure ( @MyID int)
AS
SELECT
CountryID,
OriginCityID,
DestCityID
FROM
MyTable
WHERE (MyID = @MyID)
The OriginCityID and DestCityID are related to CityID in Cities table. I wanna get the name
and url of each city by its ID through this stored procedue by making relation to Cities table.
so I call GetCityByID function in my stored procedure like this:
ALTER PROCEDURE MyProcedure ( @MyID int)
AS
SELECT
CountryID,
dbo.GetCityByID(OriginCityID),
dbo.GetCityByID(DestCityID)
FROM
MyTable
WHERE (MyID = @MyID)
this procedure dosn't work an returns error.
What's your solution for getting information from Cities table for OriginCityID and DestCityID?
Thank you in advance.