permissions with sql server tables

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.Try creating the view with the VIEW_METADATA option. This way, Access will
use view meta data instead of meta data from the underlying base tables.
See CREATE VIEW in the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ezekil" <ezekil@.lycos.com> wrote in message
news:411d05d7$0$195$cd19a363@.news.wanadoo.nl...
> 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.|||Hi Dan,

I've looked it up in BOL but it is not very clear. Could you provide me an
example?

Thnx
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:wxfTc.20463$9Y6.12982@.newsread1.news.pas.eart hlink.net...
> Try creating the view with the VIEW_METADATA option. This way, Access
will
> use view meta data instead of meta data from the underlying base tables.
> See CREATE VIEW in the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ezekil" <ezekil@.lycos.com> wrote in message
> news:411d05d7$0$195$cd19a363@.news.wanadoo.nl...
> > 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.|||Here's a simple example:

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

"Ezekil" <ezekil@.lycos.com> wrote in message
news:411df325$0$80325$a344fe98@.news.wanadoo.nl...
> Hi Dan,
> I've looked it up in BOL but it is not very clear. Could you provide me
an
> example?
> Thnx
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:wxfTc.20463$9Y6.12982@.newsread1.news.pas.eart hlink.net...
> > Try creating the view with the VIEW_METADATA option. This way, Access
> will
> > use view meta data instead of meta data from the underlying base tables.
> > See CREATE VIEW in the Books Online for more information.
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Ezekil" <ezekil@.lycos.com> wrote in message
> > news:411d05d7$0$195$cd19a363@.news.wanadoo.nl...
> > > 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.
> >