permissions with sql server tables

[Ezekiël]

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.

[Dan Guzman]

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

"Ezekiël" <ezekië[e-mail]> wrote in message
news:41*********************@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.

[Ezekiël]

Hi Dan,

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

Thnx
"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message
news:wx*******************@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

"Ezekiël" <ezekië[e-mail]> wrote in message
news:41*********************@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.

[Dan Guzman]

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

"Ezekiël" <ezekië[e-mail]> wrote in message
news:41***********************@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" <da*******@nospam-earthlink.net> wrote in message
news:wx*******************@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

"Ezekiël" <ezekië[e-mail]> wrote in message
news:41*********************@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.