permissions with sql server tables and access

Hello,
I need some help with implenting the following:
I recently migrated from access to sql server and i now i want to use
maintainable permissions on my tables, views, etc. The access database will
serve as a front-end.
I've created for testing purposes an testaccount with only a public role to
access to my database.
Now the hard part is when i want users to select and manipulate the data
through views and stored procedures.I want only permissions set on views and
stored procedures. The reason for this is because i don't want users to get
the data directly from tables by means of linking or importing them to
access
or other databases. Only views and stored procedures can be used.
Unfortunelately it doesn't work how i wanted to. When i open a view which is
linked in access as a table, i'm getting a message that the underlying table
has not the appropiate permissions.
Now there should be a way to apply a maintainable security, so if i could
have some advice and maybe an example on this matter i would be very
thankful.Create the view with the WITH VIEW_METADATA option, which will allow
users to use the view to update data. Without it, permissions on the
base tables are required. See the CREATE VIEW topic in SQL BooksOnline
for more information. If you put a Profiler trace on the Access-SQLS
app, you can see the exact calls that are being made. This will help
you troubleshoot future issues.
--Mary
On Fri, 13 Aug 2004 20:19:28 +0200, "Ezekil" <ezekil@.lycios.nl>
wrote:

>Hello,
>I need some help with implenting the following:
>I recently migrated from access to sql server and i now i want to use
>maintainable permissions on my tables, views, etc. The access database will
>serve as a front-end.
>I've created for testing purposes an testaccount with only a public role to
>access to my database.
>Now the hard part is when i want users to select and manipulate the data
>through views and stored procedures.I want only permissions set on views an
d
>stored procedures. The reason for this is because i don't want users to get
>the data directly from tables by means of linking or importing them to
>access
>or other databases. Only views and stored procedures can be used.
>Unfortunelately it doesn't work how i wanted to. When i open a view which i
s
>linked in access as a table, i'm getting a message that the underlying tabl
e
>has not the appropiate permissions.
>Now there should be a way to apply a maintainable security, so if i could
>have some advice and maybe an example on this matter i would be very
>thankful.
>|||Do you have an example? BOL is not very clear to me.
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:k37sh0hnmit3dfkgj0hk7tn7pi8cdem6g9@.
4ax.com...
> Create the view with the WITH VIEW_METADATA option, which will allow
> users to use the view to update data. Without it, permissions on the
> base tables are required. See the CREATE VIEW topic in SQL BooksOnline
> for more information. If you put a Profiler trace on the Access-SQLS
> app, you can see the exact calls that are being made. This will help
> you troubleshoot future issues.
> --Mary
> On Fri, 13 Aug 2004 20:19:28 +0200, "Ezekil" <ezekil@.lycios.nl>
> wrote:
>
will[vbcol=seagreen]
to[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
is[vbcol=seagreen]
table[vbcol=seagreen]
>|||You've asked this same question in comp.databases.ms-sqlserver. Please
don't post the same question independently to multiple groups as this causes
duplication of effort.
Here's the example I posted to that thread:
CREATE TABLE dbo.MyTable
(
Col1 int NOT NULL,
Col2 int NOT NULL
)
GO
CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT Col1
FROM dbo.MyTable
GO
GRANT SELECT ON MyView TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP