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.
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.
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.
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.
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??
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 ---
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.
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?
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
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.
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?
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?
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??
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 :(
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
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?
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.
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'.
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
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
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
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
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.
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
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?
