Accessing Views via ODBC from MSAccess

Hello,

This started as a simple thing, allow somebody to access to a database on a SQL 2005 server so they could get to it from MSAccess. I have created a login (Windows Authentication) so they can see the database and gave them "public" and "datareader" abilities.

The user then informed me that while they could see the views they could not see the data. Not being a DBA (sorry) I added "public" to the view's permissions (under properties) and granted it "Select" access and nothing else. After I completed this the user could not even see the views to pick them (via the ODBC link from MSAccess) and when I try to remove "public" from the permissions it dissapears, but when I reopen the permission "public" is back.

I then find out there is no data in the views and so the user would not have been able view anything anyway.

So what I want to do is remove "public" from the views permissions, is this possible?

Thanks for any help,

Tyrone

Hi,

naviagte in SSMS to the database open > Security > Roles > Database Roles > public > Right click Properties , Remove the permission from the public group to view the schema / Object.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Thanks for the reply... now the strnage big... when I go to the public properties I cannot really find anything to remove. Nothing is ticked, any ideas?

Tyrone

Accessing Stored Procedure parameters through XSD

Hi All,

I have created a stored procedure (in SQL Server 2005 - Developer) with input and output parameters. Please somebody let me know how I can call this store procedure from code behind using TableAdapter i.e. through XSD.

Thanks,

Long Live Microsoft ;)

You can start from here:Working with a Typed DataSet, and this tutorial should help:Working with Data in ASP.NET 2.0 :: Sorting Custom Paged Data. However if you just want to call the stored procedure from .net application, it should be?much easier to use SqlCommand/SqlDataAdapter.|||

Thanks for the response,

I have created a store procedure

CREATEPROCEDURE [GetUserDetails]

(

@.paramUserID INT ,

@.paramName VARCHAR(50) OUTPUT,

@.paramEmail VARCHAR(150) OUTPUT,

@.paramDOB DATETIME OUTPUT

…

…

--here more parameters will come

)

AS

BEGIN

SELECT

@.paramName = [Name]

,@.paramEmail =

,@.paramDOB = [DOB]

FROM

[Users]

WHERE

[UserID] = @.paramUserID

--more sql script will be here for additional parameters

END

I know we can easily call the strored procedure by using sqlcommand and adding sqlparameters etc.

But I have create the Dataset(xsd) in my application and I need to channel all my data operations through this.

I'v create a tableadapter for 'user' table and I need to add a query using existing stored proc 'GetUserDetails'. This procedure will expand cause I need to add another functionalities to it.

Please could you explaing how can I do this.

|||Well it's not a short explanation,?and?I?think?it's?better?to?follow?some?tutorial:

Working with Data in ASP.NET 2.0 :: Creating a Data Access Layer|||

Thanks Iori_Jay

Finally Igot the solution, and its working.

Myproblem was:

Executing stored procedure with inputand output parameters through XSD.

Stored Procedure

Posted earlier

User.xsd

TableAdapter: UserDetailsTableAdapter

Query: GetUserDetails(@.paramUserID,@.paramName, @.paramEmail, @.paramDOB)

Code to access outputparameter values

UsersTableAdapters.UserDetailsTableAdapterssobjGetUser =new UsersTableAdapters.UserDetailsTableAdapter();

String strName ="",

strEmail ="";

DateTime? dtDOB =DateTime.Now;

objGetUser.GetUserDetails(3/*userid*/, ref strName,refstrEmail,ref dtDOB);

Response.Write("<h3>UserDetails</h3>");

Response.Write("<strong>Name:</strong> " +strName);

Response.Write("<br/> <strong>Email:</strong>" + strEmail);

Response.Write("<br/><strong>DOB:</strong> " + dtDOB.ToString());

Although "System.String" and "string" are reference typesit showed error when "string" is used, so I used "String" instead of "string".

Added "?" to avail the reference property of value type "Datetime".