469,890 Members | 1,741 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

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.
Jul 20 '05 #1
3 2365
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Žl@lycos.com> 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.

Jul 20 '05 #2
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Žl@lycos.com> 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.


Jul 20 '05 #3
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Žl@lycos.com> 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Žl@lycos.com> 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.



Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by K Finegan | last post: by
9 posts views Thread by Nemisis | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.