Dbo Owned Stored Procedure Hitting Another User Owned Table

Aug 13, 2007

I am trying to come up with a solution that does not involve having a version of every stored procedure for every user I have...
Here is the problem...

I am going to have multiple users that need to have their own "product table". The structures are going to be the same for all. We currently only have one user and it is a DBO... all stored procedures are dbo.[sp name]... is there any way to get it so that the product table in the SP will be the user owned product table and not the dbo table??

I have tried just taking out the dbo prefix with no luck... the user's default schema will match the table they own so when they do a straight select they get the right information but it is just the SPs that I can't seem to get to work...

The only thing that I have come up with is making the SPs dynamic with having the username as parameter.

Is there anything else I can try??


and SQL 2005 SP2 on Win 2003 SP2

View 2 Replies


ADVERTISEMENT

Making Restricted User To See Only The Database That Is Owned By It.....!

Feb 7, 2008

How would I hide the sa account to be accessible under Logins for a restricted User in sql server 2005 and also other folders like Replication, Managment, etc need to be hidden too.



any idea,

thank,s

View 1 Replies View Related

How To Find Out The List Of All Tables And Views Owned By A Particular User In Any Database?

Oct 15, 2001

How to find out the list of all tables and views owned by a particular user in any database by querying the system tables?Thanks.

View 2 Replies View Related

Proper Syntax For Adding Object Name Owned By Domain User

Aug 11, 2005

I am receiving an error based on my query that is giving me the following error:


Code:


[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Config'.



I'm assuming that this is because the table was not made by dbo, but my domain account.

The query is currently defined as:


Code:


SQL = "SELECT * FROM Config"



but I'm assuming it needs to be defined with the syntax of ownername.objectname. My problem is when I change the string to this:


Code:


SQL = "SELECT * FROM sanm.APHinzeE.Config"



I get this error:

Code:


[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ''.



I tried to escape the slash using / but then I get the error on that. How do I specify my account name with the slash?

View 1 Replies View Related

Recommended Owned Of A DataBase

Jan 18, 2002

Hi,
What are current thoughts about who should own a Database?
I see 3 possibilities:
1. The DOMAINAdministrator (person wo starts up the Server at Bootup)
2. 'sa', or
3. a person/user closely tied to the database.

reasons for each?

Thanks for your opinions.
MichaelG

View 1 Replies View Related

Can't Connect To Any Database Not Owned By ServernameASPNET

Feb 9, 2007

I'm developing an intranet application and am using the Express versions of VWD and SQL Server 2005 for initial development before obtaining full licensed versions.
Database connections work fine when testing from within VWD but when testing from browser, unless the database owner is the IIS servernameASPNET, there are various errors depending on the specific connection settings, i.e.
Login failed for user ''. The user is not associated with a trusted SQL Server connection. -
Receive this error when a GridView is connected to a remote SSE db.
--- 
SELECT permission denied on object 'Table_1', database 'TEST', schema 'dbo'.
Receive this error when GridView is connected to local SSE db not owned by servernameASPNET.
--- 
 Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
This happens when using any control that is connected to SSE db created in VWD and stored in App_Data folder (owner is the user logon name)
--- 
Any help or ideas would be appreciated. I want to have all local data stored in App_Data folder.
Thank you.

View 1 Replies View Related

Look Up For DB Owner And Return Names Of DB's Not Owned By 'Sa'.....

Oct 10, 2001

Hi,
I need to write a stored procedure,that look's up the owner of each database on an server and returns the names of the databases which are not owned by SA.
Can we do this..if so how??

TIA

View 1 Replies View Related

Can't Get The Complete List Of Tables Owned By The Dbo

Oct 10, 2005

Hi,

View 12 Replies View Related

Create DB Role Owned By Public

Jul 20, 2007

I use the following script in order to create db role:

USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [public]GO

It doesn't work:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'public'.

However this code works fine:

USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [dbo]GOALTER AUTHORIZATION ON ROLE::[myRole] TO [public]GO

So the question is why?

View 6 Replies View Related

Only Members Of Sysadmin Role Are Allowed To Update Or Delete Jobs Owned By A Different Login

Mar 7, 2007



Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below

by the way I am a member of the sysadmin group

thanks in advance

km

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. 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 Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---

View 12 Replies View Related

Scheme Login Can't Access Tables Owned By Scheme

Dec 4, 2007

Hi,

I've having some baffling problems with a java applicaiton!

I have an application server called WA01 which used to access two tables on an MS SQL 2000 server via a scheme login. The two tables are:

_PurchaseOrderInterface
_PurchaseInvoiceInterface

Both tables were owned by the scheme user, with enough permissions to read & write. The java application on server WA01 could happily read the data within the tables and write a bit flag back to each row.

The MS database has been moved to a new server which no longer allows the java application on server WA01 to access the tables via the scheme login, the only way the java app can view and update the tables is by changing the owner of the tables to dbo. The new server is still MS SQL 2000, with comparible security settings.

The java app keeps complaing of an unknown source when trying to access via scheme, is this a domain trust issue between the two servers? Any ideas would be welcomed. I'm not an SQL expert but have a good grasp of the security structure etc.

Regards,

Alan

View 1 Replies View Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 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

(Could Not Find Stored Procedure ''.) When Calling A User Defined Procedure

Feb 4, 2008

Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d    

View 3 Replies View Related

"Help" File For User Stored Procedure

Mar 18, 2002

I've been administering SQL Server for quite a while, but have managed until now to stay out of the development side. I find myself finally writing some complex SProcs. What I want to do is to create a "switch", i.e., a parameter that will output a few lines of text characterizing the SProc, with such information as required & optional parameters, potential "gotchas", and a handful of examples of usage. I want this to be able to work like this:

EXEC spMySProc '?'
or
EXEC spMySproc 'help'

This has to work even if there are multiple required parameters.

Thanks for your help!

View 1 Replies View Related

Prompt User From Stored Procedure?

Feb 7, 2006

I have a stored procedure that moves specific data from several tables from a database on one server to several others (using a bunch of openrowset queries). I don't have an app to call this procedure - it's usually runs from the query analyzer and the parameter values for the procedure are passed in from there.

The procedure currently skips over recipient tables that are already populated with the data that's being moved. I want the ability to either skip the table or delete the existing records from the recpient table data based on the user's response. Therefore, I somehow need to prompt the user to get a response when the data already exists in the existing table.

Is there any way to prompt the user from a stored procedure, or do I have to re-develop the procedure in DTS or write and application?

View 2 Replies View Related

Getting Totals From A User Stored Procedure To Asp.net Code

Dec 1, 2003

Hi. I have records that have a status of either approved, not approved or unknown. I want to create a crystal report that shows a summary of the records - in short how many of each and what percentage. How can I do this? The approved, not approved and unknown are not number columns so I can't add them to get a total. I know I can create a cursor in T-SQL to get the counts but how do I send them back to the asp.net code?
---------------------
Here is part of the cursor I have created.
IF @@FETCH_STATUS = 0
BEGIN
IF @txtPrintSuit = 'U' SELECT @numUnkCount = @numUnkCount + 1
IF @txtPrintSuit = 'Y' SELECT @numSuitCount = @numSuitCount + 1
IF @txtPrintSuit = 'N' SELECT @numUnsuitCount = @numUnsuitCount + 1

FETCH curs_Briefs INTO @txtPrintSuit
END
------------
How do I get @numUnkCount, @numSuitCount and @numUnsuitCount back to the asp.net application? Can the RETURN statement hold more than one value?

THanks in advance

View 4 Replies View Related

Add User Assembly In Managed Stored Procedure

May 4, 2006

I am developing a managed stored procedure in VS.NET 2005 and I am trying to add a reference to an user developed assembly (not a system one) but adding a reference to it is not possible as it doesnt let me import assemblies but rather reference the few limited ones in a list. Why is this??

View 2 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

User-Defined-Function With-in Stored-Procedure??

Apr 14, 2008

Does MS-SQL allow us to create an user-defined function within the stored-procedure script? I have been getting errors. It's my first time using the user-defined function with stored-procedure. I welcome your help.


Code:


CREATE FUNCTION ftnVehicleYearFormattor (@sValue VARCHAR(2))
RETURNS VARCHAR(2)
AS
BEGIN
IF (LEN(@sValue) < 2)
SET @sValue = '0' + @sValue

RETURN @sValue
END



Thanks...

View 4 Replies View Related

Execute User Stored Procedure In Master

Nov 26, 2007

I need some clarification regarding the security inside of the master db.

I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.

Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?

Thanks in advance.

View 6 Replies View Related

SQL 2012 :: Executing Stored Procedure As Different User

Nov 26, 2014

I 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'.

View 2 Replies View Related

Stored Procedure Vs User Defined Functions

Apr 4, 2008

Hi All,

My question is :

Why we are using udf inside stored procedures ?
Will it make any performance faster for the stored procedure to execute ?

awaiting your reply.

Thanks
Renjith

View 6 Replies View Related

Stored Procedure And User-Defined Functions

Sep 25, 2006

lokesh writes "1) What are the differences between "Stored Procedure" and "User-Defined Functions"?

2) Places where we use/don't use Stored Procedure/User-Defined Functions."

View 2 Replies View Related

Stored Procedure To List Out User Access

Jan 19, 2008

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 Related

Stored Procedure - User Defined Function.

Jul 20, 2005

Hi.I'm really new to MSSQL, so therefore my question can sound stupid.Is it possible to use a function written in a module in MS-ACCESS in astored procedure?Or how can it be done, it is a complicated function with loop and more.I'll appreciate all answers also negatives ones.TIAJørn

View 1 Replies View Related

How To Use User Defined Function In Stored Procedure?

Mar 14, 2006

Hello friends,

I want to use my user defined function in a stored procedure.

I have used it like ,

select statement where id = dbo.getid(1,1,'abc')

//dbo.getid is a user defined function.



procedure is created successfully but when i run it by exec procedurename parameter



I get error that says

"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.getid", or the name is ambiguous."


Can any body help me?



Rgds,

Kiran.

View 3 Replies View Related

Depending On The Selection Of The User Of The Asp.net Web-form How Can I Do Changes In The Stored Procedure

May 11, 2007

Hi frdz,  I have created the following stored procedure in sql server 2005. In my database i have one option for the payment mode which can be done thru cash or credit(cheque).   I have created my web-application in asp.net with C# 2005. There i have a dropdownlist box for the user to select the option whether wants to do the payment thru cash or cheque.Depending on that selection if user selects cheque then all the reqt for cheque like it's no,dt,bankname etc...are visible.but if user selects the option as cash then the cheque details become invisible.Depending on the selection of the user of the asp.net web-form how can i do changes in the stored procedure...   i can write the condition likeif paymentmode=cash then ..........else.............but where and how can it be written ...pls tell methanxs in adv...u can go thru my below SP     ALTER PROCEDURE MiscellaneousStoredProcedure


@miscid int output,
@storename varchar(20),--store name to storeid
@accountname varchar(20),
@groupname varchar(20),
@paymentdt datetime,
@payeename varchar(30),
@paymode varchar(20),
@bankname varchar(50),
@chqdt datetime,
@chqno varchar(20),
@amt numeric(10, 2),
@bal numeric(10, 2),
@remarks varchar(50)

as

declare

@storeid int,
@accountid int,
@groupid int



begin
set nocount on
select @miscid = isnull(max(@miscid),0) + 1 from miscellaneourpay

if exists (select * from storemaster where storename = @storename)
select @storeid = storeid from storemaster where storename = @storename

if exists (select * from accountmaster where accountname =@accountname)
select @accountid = accountid from accountmaster where accountname =@accountname

if exists (select * from accountgroupmaster where groupname=@groupname)
select @groupid=groupid from accountgroupmaster where groupname=@groupname




begin transaction
insert into miscellaneourpay
(
miscid,
storeid,
accountid,
groupid,
paymentdt,
paymode,
payeename,
bankname,
chqdt,
chqno,
amt,
bal,
remarks

)
values
(

@miscid,
@storeid,
@accountid,
@groupid,
@paymentdt,
@paymode,
@payeename,
@bankname,
@chqdt,
@chqno,
@amt,
@bal,
@remarks

)
commit transaction

end 

View 2 Replies View Related

Stored Procedure And Calling User Defined Function

Sep 29, 2007

I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)

Short list of process flow:

1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient

4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page

5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

<FLOW WITH CODE AND FUNCTIONS :>

This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.

This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.

This is code in Form.asp

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check
Postback Type

'We need to load up vendors associated with the current client.

'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------

'If the call returns 1, then the employee has access.

'Otherwise, just write out "Access to this client is denied."

'CALL SP - Not sure what parameters need to go with it or its syntax

Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

'When it returns can check it here........

if ab_HasAccessToClient result is a 1 then

'boolean would be 1 so show panel

Else

'boolean would be 0 so show access denied

'allow them to go back to the original page.

end if

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

ON SQL SERVER: Stored Procedure

----------------------------------------------------------
--------------------------------

rx_sp_HasAccessToClient

CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

@EmployeeID INT,

@ClientSSN varchar(50),

@ReturnBitValue = OUTPUT

/*

' Parameters here passed via call from Form.asp - not sure what is passed
yet.

*/

AS

set nocount on

/*

Written by Mike Belcher 9/27/2007 for Form.asp

'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.

'Gets return bit and passes that back to the call from Form.asp

*/

GO

----------------------------------------------------------
--------------------------------

ON SQL SERVER: User-Defined Function

----------------------------------------------------------
--------------------------------

ab_HasAccessToClient

CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))

@ClientSSN varchar(50),

@EmployeeID,

@ReturnBitValue = OUTPUT

AS

SELECT 1

FROM tblEmployeesClients ec

INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

WHERE e.EmployeeID= @EmployeeID

AND c.InActiveClient=0

AND c.ClientSSN = @ClientSSN

'Some Code here to save result bit ..

RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

----------------------------------------------------------
--------------------------------

</FLOW WITH CODE AND FUNCTIONS :>

View 5 Replies View Related

SQL Server 2008 :: Allow User Only Alter Stored Procedure

Aug 10, 2015

I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.

I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.

View 1 Replies View Related

Stored Procedure And Calling User Defined Function

Sep 29, 2007

I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)

Short list of process flow:

1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient

4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page

5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

<FLOW WITH CODE AND FUNCTIONS :>

This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.

This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.

This is code in Form.asp

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check
Postback Type

'We need to load up vendors associated with the current client.

'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------

'If the call returns 1, then the employee has access.

'Otherwise, just write out "Access to this client is denied."

'CALL SP - Not sure what parameters need to go with it or its syntax

Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

'When it returns can check it here........

if ab_HasAccessToClient result is a 1 then

'boolean would be 1 so show panel

Else

'boolean would be 0 so show access denied

'allow them to go back to the original page.

end if

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

ON SQL SERVER: Stored Procedure

----------------------------------------------------------
--------------------------------

rx_sp_HasAccessToClient

CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

@EmployeeID INT,

@ClientSSN varchar(50),

@ReturnBitValue = OUTPUT

/*

' Parameters here passed via call from Form.asp - not sure what is passed
yet.

*/

AS

set nocount on

/*

Written by Mike Belcher 9/27/2007 for Form.asp

'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.

'Gets return bit and passes that back to the call from Form.asp

*/

GO

----------------------------------------------------------
--------------------------------

ON SQL SERVER: User-Defined Function

----------------------------------------------------------
--------------------------------

ab_HasAccessToClient

CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))

@ClientSSN varchar(50),

@EmployeeID,

@ReturnBitValue = OUTPUT

AS

SELECT 1

FROM tblEmployeesClients ec

INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

WHERE e.EmployeeID= @EmployeeID

AND c.InActiveClient=0

AND c.ClientSSN = @ClientSSN

'Some Code here to save result bit ..

RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

----------------------------------------------------------
--------------------------------

</FLOW WITH CODE AND FUNCTIONS :>

View 1 Replies View Related

How Do I Change The Type Of A Stored Procedure From User To System?

Aug 11, 2005

I built a database by using a generated script from the originaldatabase. It built the System Store Procedures as User type. How do Ichange them back to System type?

View 4 Replies View Related

Calling User Defined Function From Stored Procedure

Jul 30, 2007

When i call user defined function from stored procedure, i am getting an error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.MyFunction'.


why this error is happening?.

i am calling this way... dbo.MyFunction(param1,param2)

View 6 Replies View Related







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