Database Role - db_datareader

I am new to SQL Server.
When I add a new user (Like: Adam) to a database, I find
that Adam belongs to the "Public" database role. Can I
remove him from that Role ? If NOT, why ?
Besides, I would like to give Read access to the database,
someone suggests adding the db_datareader database role to
Adam. I would like to know does it mean that Adam can
read all Views / Stored Procedures / Tables ?
If we would like to upsize Access 2003 database to SQL
Server, does the View in SQL Server = Query in MS
Access ? Can view get parameters input ? What is the
difference between View and Stored Procedure ?
Thanks
Everyone is a member of public. You cannot remove a user from public. If
you add Adam to the db_datareader role, then he can select from all tables
and views. It does not give him EXEC privileges on stored procs.
A query in Access can be migrated to a view or a stored proc in SQL Server.
Views cannot take parameters. However, stored procs and table-valued
user-defined functions in SQL Server can.
A view is essentially a re-usable SELECT. A proc is a program that can do
just about any DML statement.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:658601c52510$a868c4b0$a601280a@.phx.gbl...
I am new to SQL Server.
When I add a new user (Like: Adam) to a database, I find
that Adam belongs to the "Public" database role. Can I
remove him from that Role ? If NOT, why ?
Besides, I would like to give Read access to the database,
someone suggests adding the db_datareader database role to
Adam. I would like to know does it mean that Adam can
read all Views / Stored Procedures / Tables ?
If we would like to upsize Access 2003 database to SQL
Server, does the View in SQL Server = Query in MS
Access ? Can view get parameters input ? What is the
difference between View and Stored Procedure ?
Thanks
|||You can't remove a user from the public role. It's a special
role and every user is a member of that role.
If a user is added to db_datareader database role, the user
can select from all user tables in the database.
A view is often like a query in Access but all Access
queries can not be made into views. So no...they aren't
necessarily equal.
There are no parameterized views in SQL Server. You can use
a table-valued user-defined function to simulate a
parameterized view.
A view is like a stored query (without parameters) or
sometimes referred to as a virtual table. Refer to books
online topic: SQL Views
for more information.
A stored procedure is one or more t-sql statements that can
be grouped together and executed as a single execution plan.
Refer to books online topic: SQL Stored Procedures
for more information.
Books online is an excellent resource. With being new to SQL
Server, you may want to take some time and read up on
concepts you aren't clear on, SQL Server architecture, etc.
-Sue
On Wed, 9 Mar 2005 17:29:52 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>I am new to SQL Server.
>When I add a new user (Like: Adam) to a database, I find
>that Adam belongs to the "Public" database role. Can I
>remove him from that Role ? If NOT, why ?
>Besides, I would like to give Read access to the database,
>someone suggests adding the db_datareader database role to
>Adam. I would like to know does it mean that Adam can
>read all Views / Stored Procedures / Tables ?
>If we would like to upsize Access 2003 database to SQL
>Server, does the View in SQL Server = Query in MS
>Access ? Can view get parameters input ? What is the
>difference between View and Stored Procedure ?
>Thanks
|||Thank you for advice from both of you.
Does the VBA codes in MS Access will be migrated as User
Defined Function in SQL Server ?
Thanks
|||If you use the upsizing wizard, it won't do anything with
modules or macros.
-Sue
On Wed, 9 Mar 2005 19:50:36 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>Thank you for advice from both of you.
>Does the VBA codes in MS Access will be migrated as User
>Defined Function in SQL Server ?
>Thanks
|||Thank you for your advice.
However, if I have to migrate those codes, should I use
User Defined Functions ?
Thanks

>--Original Message--
>If you use the upsizing wizard, it won't do anything with
>modules or macros.
>-Sue
>On Wed, 9 Mar 2005 19:50:36 -0800, "Paul"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>
|||Not necessarily and most likely you won't find that you
could use much, if any, of your VBA code as user defined
functions. They aren't really equivalent.
-Sue
On Wed, 9 Mar 2005 20:16:19 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thank you for your advice.
>However, if I have to migrate those codes, should I use
>User Defined Functions ?
>Thanks