I have found that table A had SELECT permissions for 'Public' but not table
B.
Giving 'Public' SELECT permissions on table B did the trick.
HOWEVER, I don't want anyone to be able to do a direct SELECT on table A or
B but only give them access to the data by using the stored procedures. Is
there any way this can be set up?
Thanks for your efforts!
Have a nice day!
Martin
"Martin Feuersteiner" <theintrepidfox@hotmail.com> wrote in message news:...[color=blue]
> Dear Group
>
> I'm having two stored procedures, sp_a and sp_b
>
> Content of stored procedure A:
> CREATE PROCEDURE dbo.sp_a
> SELECT * FROM a
> GO
>
> Content of stored procedure B:
> CREATE PROCEDURE dbo.sp_b
> SELECT * FROM b
> GO
>
> I have created a user that has execute permissions for both procedures.
> When I run procedure A, all works fine but when running procedure B I'm
> getting an error saying that the user must have SELECT permissions on
> table B.
>
> Both tables are owned by dbo, and the security role for the user doesn't
> has any SELECT permission on table a and b.
> I'd be grateful if anyone could point me in a direction why this error
> might come up for procedure B but not for A,
> with a possible solution without giving the user SELECT permissions.
>
> Thanks very much for your help!
>
> Martin
>[/color]
How does one get the names and data types of the parameters of a SQL 2005 stored procedure? There does not seem to be a refresh for a command parameters object, and the syntax for the get() in I don't seem to have right in c++.
In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would then be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
So I want a SQL procedure where it takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~ CREATE PROCEDURE VerifyIfInvoiceExists (@Invoice VARCHAR(50)) AS SELECT COUNT(*) FROM IPN_received WHERE Invoice = @Invoice ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
i want to have a user defined function or stored procedure(i don't know which one should be used, anyway!) which returns false if the table has no records, with the table name comes with a parameter.
How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?
Is it a safe way to use a paramter (which fetch values from querystring) in the "where-part" of my stored procedure? Or is it an securityrisk because I dont know what the user is writing in the url-field? I got the following sqldatasource which grab the value (from querystring) into the my parameter. <asp:SqlDataSource ID="SQLDataSource" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" runat="server" SelectCommand="My_StoredProcedure" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:QueryStringParameter QueryStringField="Myparameter" Name="City" Type="string" /> </SelectParameters> </asp:SqlDataSource>
this should be a easy question, but I can't really seem to find anything on it...
Here's the scenerio:
n-tier web app, with asp/iis/sql... All database calls are done via stored procedures with the same user (lets call the user: webuser)
webuser has NO access to the db in question, but it is granted EXEC on all stored procedures.
My question is, when a user tells the web app to say delete a record, the application server (iis) makes a call to the database with the webuser security cred's... It says execute the delete stored proc.
webuser has the ability to do this, so it happens. However, in what context (this may not be the right word) does the stored procedure execute?
ie: which user does the stored proc exeucte as. It can't be webuser can it? Because webuser does not have access to the base tables.
Does the stored proc execute as the user that created it?
I have a Stored Procedure in one database that grabs data from another database. I don't want the user to be able to read data from the tables that the stored procedure Selects from but I would like the user to be able to run the stored procedure. Pretty standard request I think.
What I have done is to give the user in question a login then assigned them the Execute Permissions on the stored procedure. Unfortunately they are still unable to run the stored procedure from my web app. I have "allow anonymous access" turned on but I am still getting an error when the user tries to execute the stored procedure.
Am I missing something here or could there be a bigger issue?
I have a role called 'creator'. I want that no one else other than creator should be able to execute this procedure through an asp.net application or directly.If anyone attempts, it should be logged.
HOw do I do this. Please explain the answer. I am new to this.
Does anyone know how you can distribute Stored Procedures that work with a third party application but keep the contents / code of those Stored Procedures from being viewed and edited by the end users who have purchased the application as well as own and operate the SQL Server??
I have an ASP page which is supposed to populate a dropdown based on values returned from a stored procedure. This ASP page is working on our old server. I recently moved all the pages, code, ect over to our new server including migrated to SQL Server 2005 (from 2000 I think), as you can tell, somthing broke...
I assume this is security related however, all the relevant security is set correctly as far as I can tell. I am granting "Execute" access for the stored procedure and "Select" access for all the referenced tables. Am I missing something? Is there another security setting which needs to be set?
here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@pswd varchar(20),@no_go int OUTPUTASSET NOCOUNT ONSELECT user_id FROM profileWHERE user_id=@id AND pswd=@pswdIF @@ROWCOUNT = 0BEGINSET @no_go = 1ENDELSEBEGINSELECT date,date_mod FROM ansWHERE user_id=@idSET @no_go = 0ENDUsing the PERL odbc_more_results function I can retrieve the data inthe second select statement whether the rowcount is 0 or not. Anysuggestions how to stop this
Dear GroupI'm having two stored procedures, sp_a and sp_bContent of stored procedure A:CREATE PROCEDURE dbo.sp_aSELECT * FROM aGOContent of stored procedure B:CREATE PROCEDURE dbo.sp_bSELECT * FROM bGOI have created a user that has execute permissions for both procedures.When I run procedure A, all works fine but when running procedure B I'mgetting an error saying that the user must have SELECT permissions on tableB.Both tables are owned by dbo, and the security role for the user doesn't hasany SELECT permission on table a and b.I'd be grateful if anyone could point me in a direction why this error mightcome up for procedure B but not for A,with a possible solution without giving the user SELECT permissions.Thanks very much for your help!Martin
Hi,I have a .NET application that connects to a SQL 2000 database usingtrusted security. It eventually calls a stored procedure that receives3 parameters - nothing special.If I simply change the connection string to use a valid Userid andPassword it still connects to the DB w/o problems but when it executesthe SP I get the following:System.Data.SqlClient.SqlException: Invalid length parameter passed tothe substring function.I change nothing but the login. Same store procedure, same parameters.Any ideas?
Hi all . I am going to consult you about a security concept . In my database I have a set of tables (eg. City , Country , ...) and I have a corresponding View for each Table (eg. V_City , V_Country , ...) and There are a set of Add/Delete/Update/List stored procedures for each object , the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security? Not that I access SQL Server Database from a .Net App.
I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?
I backed up a database from one server and restored in it on another server the database was installed with data, but the security info did not get across.
"Could not impersonate the client during assembly file operation."
The CLR function is invoked from Service Broker internal activation stored procedure.
"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.
SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.
How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?
Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".
We have 2005 Reporting Services fully functional - Dev, QA and prod (farm). I have at least 5 distinct business group trees in my structure, who have inherited or modified security based upon NT Users and AD groups. Now, we want to replace a major group (<domain_name>Domain Users) with a controlled group.
I cannot find anyway to report or map the folders that a specific NT User and AD group has rights. Cannot it be done?
Hi, The Report Manager portal has many folders. For each folder there are specific users with different roles. I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.
Use ReportServer
SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r
WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID The above query fetches all the users and their roles. The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query. TIA
In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
So I want a SQL procedure where it takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~ CREATE PROCEDURE VerifyIfInvoiceExists (@Invoice VARCHAR(50)) AS SELECT COUNT(*) FROM IPN_received WHERE Invoice = @Invoice ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I know how to look at active users from Enterprise Manager, but how can I query out the information - what table is that info stored in ??? I don't want a list of all the logins, I just want the list of active users....
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
