limit user account to a table or database or view

Hello I think i have read in other posts that there is not way in
sql200 to limit access to a view to a certain user. What work arounds
are there for this, Can I schedule a dts export to a different database
or table and the assign rights only to data database or table? Can
anyone give some steps to create a secure login to a single sql view?
thanksIn SQL Server 2000, a user in a database does not have any access to an
object (table, view, proc, etc.) unless that user 1) owns it, 2) they
have been granted explicit permissions to it or 3) they have implicit
permissions to it via their role membership within the DB.
If you want a single DB user to only have access to a single view in a
database and no other database objects in that database, you just grant
them the appropriate permissions to that view with the GRANT statement
(presumably we're talking about SELECT permissions here), make sure the
"public" role in the database has no explicit permissions to anything
(which, by default, it doesn't) and make sure the user is not a member
of any other role in the DB (every user in a database is a member of
"public", you can't change that). This will guarantee that the only
object the user can interact with in the DB is that view.
Is that your goal? (Also, are you talking about SQL 2000 or SQL 2005?)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
joe wrote:

>Hello I think i have read in other posts that there is not way in
>sql200 to limit access to a view to a certain user. What work arounds
>are there for this, Can I schedule a dts export to a different database
>or table and the assign rights only to data database or table? Can
>anyone give some steps to create a secure login to a single sql view?
>thanks
>
>|||Hi,
Nope its not possible to in SQL 2000 but you can restrict your users to
access it using sp_revokedbaccess , offcourse they can see that this db
exists in dblist but they wont able to access it . this is a fixed in SQL
2005.
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"joe" wrote:

> Hello I think i have read in other posts that there is not way in
> sql200 to limit access to a view to a certain user. What work arounds
> are there for this, Can I schedule a dts export to a different database
> or table and the assign rights only to data database or table? Can
> anyone give some steps to create a secure login to a single sql view?
> thanks
>|||I think we need some clarification from the OP as to what he's trying to
achieve. Joe, what exactly are you trying to do? Are you trying to
*hide* a database from various SQL server logins in SQL Enterprise
Manager (SQL 2000) on a particular server (Andy's reply)? Or are you
trying to make sure a particular database user *can only access a
single*, specific object within the database (my original reply)? Or
are you trying to make sure that a particular database user *cannot*
access a particular database object within the database (this can be
done with the DENY statement)?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Andy Davis wrote:

>Hi,
>Nope its not possible to in SQL 2000 but you can restrict your users to
>access it using sp_revokedbaccess , offcourse they can see that this db
>exists in dblist but they wont able to access it . this is a fixed in SQL
>2005.
>Regards
>|||Hi,
true we need to wait for his response.
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Mike Hodgson" wrote:

> I think we need some clarification from the OP as to what he's trying to
> achieve. Joe, what exactly are you trying to do? Are you trying to
> *hide* a database from various SQL server logins in SQL Enterprise
> Manager (SQL 2000) on a particular server (Andy's reply)? Or are you
> trying to make sure a particular database user *can only access a
> single*, specific object within the database (my original reply)? Or
> are you trying to make sure that a particular database user *cannot*
> access a particular database object within the database (this can be
> done with the DENY statement)?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Andy Davis wrote:
>
>|||I know this question is old and that comments are not likely to be helpful,
but my interpretation of the question was that Joe wants a particular user
to be able to access a specific view but all other users should not have
access to the view. My understanding of the requirement is that the
requirement does not prevent a single user from having access to anything
else.
It is certainly best to get the requirements clarified before attempting to
suggest solutions.
I did not quote Mike's comment because it is posted using HTML format and
when I replied to it Outlook Express did not provide for it the quotation
symbols at the left of quoted text.