How Can I Execute Stored Procedures With Correct User Rights?

Sep 3, 2007

Hi,

I have a problem with sp execution.:







objects of [dbo]

Tables of [nuran]

Grants of [nuran]


[dbo].tabloA

[nuran].tmptabloA

Select,insert, update on [dbo].tabloA


[dbo].tmptabloA



Deny for [dbo].tmptabloA


[dbo].sp_yordam



Grant for executing [dbo].sp_yordam









(1)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN

BEGIN TRANSACTION @Tran1
€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦ €¦€¦€¦€¦.

INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [tmptabloA] WHERE ......

€¦€¦€¦ €¦€¦€¦ €¦€¦€¦.

DELETE FROM [tmptabloA]

COMMIT TRANSACTION @Tran1


When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].

If I write the procedure like that:

(2)
create PROCEDURE [dbo].[SP_tmpSil]
AS

declare @tablo1 as varchar(50),
DECLARE @sil as nvarchar(max)
select @tablo1='[tmptabloA]'

SELECT @sil = ' DELETE FROM ' + @tablo1 + ';'
EXEC (@sil)

END

And it executed by user [nuran],then it used the correct table [nuran].tmptabloA

Is there any way to use user€™s table in an stored procedure without using the user name :
(3)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN

BEGIN TRANSACTION @Tran1
€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦ €¦€¦€¦€¦.

INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [nuran].[tmptabloA] WHERE ......

€¦€¦€¦ €¦€¦€¦ €¦€¦€¦.

DELETE FROM [nuran].[tmptabloA]

COMMIT TRANSACTION @Tran1


I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?

Thanks a lot

Nuran

View 4 Replies


ADVERTISEMENT

SQL 2012 :: Login / User Cannot Execute Or See Stored Procedures

May 6, 2014

We are using a third party tool that does not store passwords in an encrypted format therefore we created a user with minimal rights. Isn't there a way to grant "execute any stored procedure" to a user/Login? Do we really have to grant execute on each procedure to the user? And then do the same for each new store procedure? The only other option we have found to be able to "see" and execute the stored procedures is by granting "db_owner". I would think that would negate the user being minimal rights.

View 5 Replies View Related

Cannot Debug Stored Procedures Because The SQL Server Database Is Not Setup Correctly Or User Does Not Have Permission To Execute Master.sp_sdidebug.

Aug 7, 2007

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.

View 3 Replies View Related

Minimum Set Of Permissions And User Rights To Execute A Report Definition

Jan 28, 2008

To use Reporting Services as a rendering engine I want to configure a local user on the server that has only the minimum set of permissions and user rights. The server is W2K3 SP2 and SQL 9.0.3200.

In particular, this local user has been removed from the local "Users" group and so is the "Authenticated Users" built-in group. In Reporting Services, it is mapped to a role that only has the "Execute Report Definitions" task permission.

Then, following the details in http://support.microsoft.com/kb/812614/ (Default permissions and user rights for IIS 6.0) I added all file security and local user rights required for "Users" and also granted and propagated "Read&Execute" on the "Reporting Services" folder and verified this using "Effective Permissions" on the ReportService2005.asmx file.

However, I still get 401 Unauthorized, also after a complete restart of all related machines and services.

Once I add the user or "Authenticated Users" back to "Users" everything works fine.

What permissions might I be missing? Where could I find those permission requirements documented?

I tried analyzing the 401 using auditing file and object access security but to no avail. There are no Failure audit entries in the Security log.


How can I investigate the minimum permission set?

What is the risk of leaving the user in the "Users" local group?


Any help appreciated.

View 1 Replies View Related

How To Grant Rights For The Anonymous IIS Web User IUSR_.. To Execute Scalar Function In Assembly

Jul 26, 2006

Dear all,

Basically I want to set chain up the rights so that the anonymous web user IUSR_ .. can execute the new .NET subs, functions etc in the assembly, just as the anonymous web user can execute Stored Procedures when granted. In this way, it should be possible to call the .NET assembly just as classic stored procedures from ASP/ASP.NET.

I have written a .NET function which I can successfully execute if I log on to the database as an administrator by sending this T-SQL query; it returns the result of a given string:

select dbo.CLRHTMLString('abc')

The scenario is now to try to grant access to this assembly for a different role (webuser), which the classic IUSR_MYSERVERNAME is a login of, so that I can call the .NET Assembly when I am authenticated as the anonymous web user (e.g. via ASP, etc.).

To test access, I created a login (webusertest) for a user (webusertest) in the same role (webuser) on the database. But when I use this login, which supposedly has the same rights as the IUSR_, execution right is denied:

EXECUTE permission denied on object 'CLRHTMLString', database 'adt_db', schema 'dbo'.

Note: The 'webuser' database role has Execute permission on the Assembly.

I have also tested this from my actual web page, with the following results:
(1) IUSR_MYSERVER member of db_owner role: Web page has right to call assembly.
(2) IUSR_MYSERVER not member of db_owner role: Web page does not have right to call assembly.

Further test results:
(3) Function can be called when making the user "webusertest" member of the "db_owner" role, which is too much rights to grant for the anonymous web user.

(4) When adding the user 'webusertest' to get 'Execute' permissions on the assembly, it does not get added. After clicking OK, there is no warning message, but when opening the Assembly Properties -> Permission dialog box the same time, the 'webusertest' user does not appear in the list.

Thankful for any advice on this matter.

View 4 Replies View Related

SQL 2012 :: Access Rights To Execute Stored Procedure If There Is No DML Involved

Jul 2, 2014

I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a database
But, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures

I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working

Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.

View 7 Replies View Related

Stored Procedures -- Correct Practice?

Jan 16, 2008

Hi,

I have 3 tables: authors, companies, and countries.

I have a stored procedure defined as:

PROC addAuthor(AuthorName, CompanyID, CountryID)
INSERT INTO authors (author_name, comp_id, country_id) VALUES( authorName, CompanyID, CountryID)
END PROC

....So the ID of the company and the country are the parameters. This makes it easy since I can just do a direct INSERT statement into the authors table.

Would it be better practice if the parameters asked for the name (not the ID's) of the company and country instead? This way we do not have to memorize the ID's for everything. So for example

PROC addAuthor(AuthorName, CompanyName, CountryName)
SELECT COUNT(*) FROM companies INTO v_numRows WHERE companies.company_name = CompanyName
IF v_numRows = 0
-- error
END IF

SELECT COUNT(*) FROM countries INTO v_numRows WHERE countries.country_name = CountryName
IF v_numRows = 0
-- error
END IF
END PROC

yeah, I might have the SQL syntax wrong up there (not entirely sure how it works for variables) but the gist of it is that I will need to include validation code within the stored procedure.

What do you guys think of this? Which method do you prefer? Is there a significant performance decrease with using the second method?

Thanks for any insights

View 8 Replies View Related

Determine Correct Flow Of Stored Procedures

Mar 12, 2015

We have some pre-defined stored procedures(around 4-5) which deletes/ truncates the tables.

- Is there a way I can know the correct order of running the SP.May be I should check that child tables are deleted first and then the parent tables.

- How can I test the same. Since I am not aware of the flow thereby should I try running with NO eligible records for deletion. Will this ensure that I have sufficient privileges for the SP or do I need to delete a record (may b an older record).

View 3 Replies View Related

Stored Procedure (DB User/login) Rights

Dec 29, 2004

Does anyone have a SP which will output all users and their permissions (dbo), (read), (write) for each database for SQL 2k? I'm trying to find a way to simply automate this so I can output the data on demand for auditors. I'm currently checking each users permissions on each database through security/logins :(

View 3 Replies View Related

Sysdepends And Compiling Stored Procedures In The Correct Order

Nov 9, 2000

Hello:

We are presently testing various upgrade scripts to our current application which is scheduled to shortly be upgraded to mssql 7.0. We are testing under mssql 7.0,sp 2.

As it works now, I receive some existing scripts that have been modified and some stored proceures that are new. For the existing stored procedures, I usually take my best guess as to what the order of creation will be, test it in my script for recompile(actually all are dropped first and then the guesswork on the creates). This is usually trial and error as I run the script, see any sysdepends errors such as:

"CREATE PROCEDURE: ep_invoiceheaderformat_spv0101
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ep_assumepay"

And then move the order of the create procedures around in the script and try again until I get a clean run in a test database I use just to syntactically test the scripts.

I looked at the sysdepends table for the database and pretty much decided that the object numbers and stuff was pretty much incomprehensible to me.


Alternatively I could compile each one separately but I would have the same problem subsequently trying to generate a script of the al of the create procedures... in the right order which would not

My question is:

1) Is there a way I can read and understand what the data means in sysdepends?

2) Figure out a way to utilize the data there to create or generate the create stored procedure text in the correct clean compile order?

3) Any other suggestions?

Any information which can be proveded will be greatly appreciated. Thanks.


David Spaisman

View 1 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Execute Permission For Stored Procedures

Jan 14, 2005

Hello 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!

View 5 Replies View Related

System Stored Procedures Do Not Execute

Oct 23, 2006

I tried this morning to check some of the system stored procedures and ran into trouble. Only four of them executed: sp_alterdiagram, sp_creatediagram, sp_dropdiagram and sp_helpdiagramdefinition. I could not check all the rest, I did it selectively. The typical error message was: Invalid object on RETURN statement. Some had syntactical errors.
sp_ActiveDirectory_Obj
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_SCP
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_Start
Invalid object name 'sys.sp_ActiveDirectory_Start' Line 19
Invalid object name 'sys.sp_add_agent_parameter' Line 60
Invalid object name 'sys.sp_add_agent_profile' Line 123
Invalid object name 'sys.sp_add_data_file_recover_suspect_db' Line 17
sp_addalias
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 44
Incorrect syntax near '%'.
Msg 195, Level 15, State 10, Procedure sp_addalias, Line 64
'get_sid' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 78
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 119
Incorrect syntax near '%'.
sp_bindefault
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 95
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 134
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 182
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 208
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 228
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 264
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 273
Incorrect syntax near '%'.
sp_databases
Invalid object name 'sys.sp_databases'. Line 6
sp_tables
Invalid object name 'sys.sp_tables'

Is there a chance that these errors are due to the fact that I executed them without parameters?

Thanks

View 2 Replies View Related

Execute Permissions On Stored Procedures

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

Adding Execute Permission To Stored Procedures

Jun 14, 2006

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

View 1 Replies View Related

Execute Stored Procedures And Combine Results

Oct 17, 2005

I have created multiple stored procedures that search different tables for similiar information.

Is it possible to have one main stored procedure that calls and executes each of these individual stored procedures and then use the UNION keyword to combine the results?

For example


Code:

CREATE PROCEDURE [dbo].[Return_Detail]

@IDVarChar(200)

AS

--Get the 1st Detail
EXECReturn_1st_Detail @ID = @ID

UNION

--Get the 2nd Detail
EXECReturn_2nd_Detail @ID = @ID
GO

View 2 Replies View Related

Adding Execute Permissions On Stored Procedures

Jul 23, 2005

How can i add Execute permissions on the Stored Procedures under thecreated user permission iusing SQLDMO ?

View 1 Replies View Related

How Do I Give EXECUTE Permissions On Stored Procedures?

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

DTS - Using Multiple Stored Procedures In One Execute SQL Task

Jan 3, 2008

I'm running this legacy DTS package under SQL Server 2005, and need to make some changes.

I have some (say, 3) stored procedures, and they all have one same parameter. Each SP returns a dataset. I need to import each dataset to a table using DTS. Both the SPs and the destination tables are in the same database.


So, I created an "Execute SQL Task" and typed in "SQL Statement" something like this:


INSERT INTO TABLE TABLE1
EXEC SP1 ?
GO
INSERT INTO TABLE TABLE2
EXEC SP2 ?
GO
INSERT INTO TABLE TABLE3
EXEC SP3 ?
GO


And the execution returns an error: No value given for one or more required parameters.


Is it possible to assign a parameter to all three SPs? What is the work out if I don't want to change the SPs, and I don't want to create 3 Execute SQL Tasks?

View 4 Replies View Related

Using The SQLDMO Reference To Create SQL Stored Procedures On-The-Fly And Execute

Nov 6, 2007

I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object.
 Here are some clipits of what you need.
Imports SQLDMOPrivate myServer As New SQLServer
Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString()
Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString()
Dim storedProcedure As New StoredProcedure
 Dim qresults As SQLDMO.QueryResults
Example Stored Procedure
Try
myServer.Connect(serverName, userName, password)
storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
Label1.Text = "Creation of stored procedure successful"Catch ex As Exception
Label1.Text = "Creation of stored procedure failed"
Finally
myServer.DisConnect()End Try
 Run Stored Procedure and Obtain Results
myServer.Connect(serverName, userName, password)
qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows
MemberRowNo = qresults.GetColumnString(num, 1)
Next
myServer.DisConnect()

View 3 Replies View Related

How To Execute DBCC Statements Through Remote Stored Procedures

Jan 23, 2004

Hello,

Is it possible to execute DBCC statements remotely in destination servers through stored procedures existing in source server.

I don’t want to use isql, osql, openquery, openresultset because I have to pass user id or password.

I have linked the required SQL servers using Linked servers.

If so please give some example.

Thanks in Advance,
Sateesh

View 9 Replies View Related

SQL Server 2008 :: Execute Stored Procedures Dynamically

Mar 5, 2015

I have a column in a table, which have the stored procedure name stored in each row. Now, I need to execute each SP in the table dynamically. I'm trying to construct a SQL but not able to fire them!!

DECLARE @sql VARCHAR(MAX)
SELECT @sql = STUFF((SELECT '; GO EXEC ' + StoredProcedureName + '' FROM MyTable FOR XML PATH ('')),1,5,'')
print @sql
EXEC sp_executesql @sql

View 2 Replies View Related

SQL2005 Stored Procedures Don't Execute After Attach Database

Sep 19, 2006

While doing maintenance on a development server we detached a database and later reattached it to the same server.

For some unknown reason web services execute but return no results. There are no errors reported in event viewer. I connected to the database in Visual Studio 2005 and attempted to execute a simple select statement but it returns no row.

It appears to execute the stored procedure but does not return any data. I have cut and pasted the select statement into a View which does execute.

Lastly I just created a new store procedure to test the database and it also does not return any rows. This is all very strange.

Could use a few suggestions at this point.

Larry

View 2 Replies View Related

Using Sql Server Agent Stored Procedures To Execute A Package

Aug 15, 2006

hi everyone,

awhile back someone (i think it was darren) submitted a post which had a code example of using sql server agent stored procedures to execute a package.  i searched for the post, but i didn't find it.

anyway, can someone please re-post that example or provide a similar one?

thanks.

View 7 Replies View Related

Execute SQL Task - Parameter Mapping For Stored Procedures

Dec 17, 2007

Hi ALL,

I have a Execute SQL Task to execute a stored procedure. It has no input and output parameters.

The stored procedure is defined as follows:


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SetSLATimePriority]

AS

BEGIN

DECLARE @PriorityID tinyint,@MAXPriorityID tinyint

DECLARE @Priority NVARCHAR(MAX), @SLATime int

SET @PriorityID=1

SET @MAXPriorityID=0

SELECT @MAXPriorityID=MAX([Priority ID]) FROM [MTD Dashboard].[dbo].[Priority]

SET NOCOUNT ON;

WHILE @PriorityID<=@MAXPriorityID

BEGIN

SELECT @Priority= [Priority] FROM [MTD Dashboard].[dbo].[Priority]

WHERE [Priority ID]=@PriorityID

SELECT @SLATime= [SLA Time in hours] FROM [MTD Dashboard].[dbo].[Priority]

WHERE [Priority ID]=@PriorityID

UPDATE [MTD Dashboard].[dbo].[Remedy Dump-Filtered]

SET [SLA Time] = @SLATime WHERE [Priority] like @Priority

SET @PriorityID=@PriorityID+1

END

END


The Properties of Execute SQL Task are set as follows:

Result Set: None
Connection Type: OLEDB
SQL Source Type: Direct Input
SQL Statement: EXEC ? = [dbo].[SetSLATimePriority]
IsQueryStoredProcedure: True
ByPassPrepare: False

Parameter Mapping:

Variable Name : User::IntValue
Direction: ReturnValue
Data Type: Long
ParameterName: 0

I am getting the following error, when I run this package.

[Execute SQL Task] Error: Executing the query "EXEC ? = [dbo].[SetSLATimePriority]" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am not able to figure out, where exactly the problem is.. Can some one please help me out?

View 3 Replies View Related

Using EXECUTE Statements Calling An Extended Stored Procedures From Function..

Apr 29, 2004

Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)

Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS

RETURN Exec spTest @from, @to
GO

Yes, it give syntax error..

2. So, I found the following


From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..

"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.

So, I tried.

alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS

RETURN Exec master..xp_msver
GO

It doesn't work... syntax err...

Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?


Now, I'm stuck.. how can I create dynamic select statement using function?

I want to know if it's possible or not..

View 13 Replies View Related

Integration Services :: Execute Stored Procedures In Parallel And Dynamically?

Feb 19, 2015

I have a stored procedure. It can be executed like this

exec test @p = 1;
exec test @p = 2
exec test @p = n;
n can be hundred.

I want the sp being executed in parallel, not sequence. It means the 3 examples above can be run at the same time.
If I know the number in advance, say 3, I can create 3 different Execution SQL Tasks. They can be run in parallel.

However, the n is not static. It is coming from a table.Β 

How can I execute Stored Procedures in PARALLEL and DYNAMICALLY ?

I think about using script task. In the script, I get the value of n, and the list of p, from the table, then running a loop with. In the loop, I create a threat and in the threat, I execute the sp like :Β exec test @p = p. So theΒ exec test may be run parallel. But I am not sure if it works.

View 4 Replies View Related

SQL Server Admin 2014 :: Replication Could Not Find Or Execute Stored Procedures

Jul 20, 2015

Replication-Replication Distribution Subsystem: agent PRODDATA6 failed. Could not find stored procedure 'sp_MSdel_dboActiveTask_msrepl_ccs'.

Replication-Replication Transaction-Log Reader Subsystem: agent PRODDATA6. The process could not execute 'sp_replcmds' on 'PRODDATA6'.

Replication-Replication Distribution Subsystem: agent PRODDATA6. Could not find stored procedure 'sp_MSupd_dboActiveTask'.

Looks like these sp's got deleted?

View 1 Replies View Related

User Stored Procedures

Nov 30, 2006

is 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?

View 16 Replies View Related

User Stored Procedures

Jul 10, 2007

Dear all,
How to find out how many user stored procedures are there in a single Database in sql server.


Thanks,
msrs

View 6 Replies View Related

Using User Functions In Stored Procedures

Feb 1, 2005

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.

View 1 Replies View Related

SQL 2012 :: User Is Not Able To See Stored Procedures

Jun 12, 2015

user have db_datareader role in in a database.But user is not able to see the stored procedures.

View 5 Replies View Related

User Who Can Connect/run Stored Procedures Only?

Nov 6, 2007

I'm new to 2005 (new to sql server in general). Up until now it's only been writing easy stored procedures. Read that it's a good idea to have a user who can only run stroed procedures (so aren't connceting with 'sa' privileges and such). How would I go about doing this?

The server I am working on has about 15 different databases on it. My database (named "myDBForTest") is the only one I want my end user to be able to connect on. I'm writing a .NET application that will have several users using it at same time. They will all be connecting with same information (limited to only running stored procedures and connecting/disconnecting). I basically want them to be able to run any stroed procedures that are part of my database. If I go back in 4 months and add 100 stored procedures, the next time the log on they should be able to use those new ones also.

Is there some kind of command I run to add a user (maybe with username of sprocUser?) who can do this? I'm interfacing with database using SQL Server Management Studio Express (from MS).

Thanks for any help you can give.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved