If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.com
Robert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com
|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
Showing posts with label role. Show all posts
Showing posts with label role. Show all posts
Sunday, March 11, 2012
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitl
y
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, o
r
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put
a
> check mark (such as select, insert, update, delete) for access to a table,
or
> Exec for a stored procedure for the user to have those specified permissio
ns
> on the object, because when the user is granted the database owner role, a
ll
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitl
y
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, o
r
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put
a
> check mark (such as select, insert, update, delete) for access to a table,
or
> Exec for a stored procedure for the user to have those specified permissio
ns
> on the object, because when the user is granted the database owner role, a
ll
> the check boxes are blank.
Database Roles
In SQL Server 2005, I want to create a database role and then create
additional roles based on the earlier role (sort of an inheritance approach
to creating roles). However, I can't seem to find a way to make one role a
member of another, share schema... Is this possible or advisable?
Michael HocksteinHi Michael,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to
assign a database role to be a member of another database role. If I
misunderstood your concern, please feel free to let me know.
You can not assign a database role to be a member of another database role.
If you want to grant the same permission of another database role and
extend the permission, please grant the CONTROL permission to the database
role for your additional database role.
Thank you for taking the time to provide feedback on this product.
We are very interested in your thoughts and opinions for improvements that
we can make to provide the features and functionality you and your
customers would like to see.
To provide your feedback, please go the following website:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Database Roles
>thread-index: AcZtT6Kc1Lw4anhJSDuWBEEKvOfeXA==
>X-WBNR-Posting-Host: 198.133.139.5
>From: examnotes <howlinghound@.nospam.nospam>
>Subject: Database Roles
>Date: Mon, 1 May 2006 11:47:02 -0700
>Lines: 7
>Message-ID: <DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.security
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.security:27213
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.security
>In SQL Server 2005, I want to create a database role and then create
>additional roles based on the earlier role (sort of an inheritance
approach
>to creating roles). However, I can't seem to find a way to make one role a
>member of another, share schema... Is this possible or advisable?
>--
>Michael Hockstein
>|||So, if I execute a statement such as:
GRANT CONTROL ON Role1 TO Role2
Go
then anything that Role1 had permissions to would be applied to Role2 as wel
l?
Michael Hockstein
"Wei Lu" wrote:
> Hi Michael,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: You want to
> assign a database role to be a member of another database role. If I
> misunderstood your concern, please feel free to let me know.
> You can not assign a database role to be a member of another database role
.
> If you want to grant the same permission of another database role and
> extend the permission, please grant the CONTROL permission to the database
> role for your additional database role.
> Thank you for taking the time to provide feedback on this product.
> We are very interested in your thoughts and opinions for improvements that
> we can make to provide the features and functionality you and your
> customers would like to see.
> To provide your feedback, please go the following website:
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> approach
>|||Hi Michael,
As I mentioned in the previous post, you can not grant the same permission
of a database role directly.
My suggestion is, you could check the sys.database_permissions catalog view
to see what the permissions does the original role grant and you could
grant the additional role the same permissions.
Grant the control permission to a database role will not grant the same
permission on database.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||"michael" <howlinghound@.nospam.nospam> wrote in message
news:DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com...
> In SQL Server 2005, I want to create a database role and then create
> additional roles based on the earlier role (sort of an inheritance
> approach
> to creating roles). However, I can't seem to find a way to make one role a
> member of another, share schema... Is this possible or advisable?
>
Not possible, but:
With the ability to grant on the database or schema level, the number of
indvidual grants necessary to implement security is typically much reduced.
You can add users to multiple roles, so the incremental rights can be
attached to an additional role.
David
additional roles based on the earlier role (sort of an inheritance approach
to creating roles). However, I can't seem to find a way to make one role a
member of another, share schema... Is this possible or advisable?
Michael HocksteinHi Michael,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to
assign a database role to be a member of another database role. If I
misunderstood your concern, please feel free to let me know.
You can not assign a database role to be a member of another database role.
If you want to grant the same permission of another database role and
extend the permission, please grant the CONTROL permission to the database
role for your additional database role.
Thank you for taking the time to provide feedback on this product.
We are very interested in your thoughts and opinions for improvements that
we can make to provide the features and functionality you and your
customers would like to see.
To provide your feedback, please go the following website:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Database Roles
>thread-index: AcZtT6Kc1Lw4anhJSDuWBEEKvOfeXA==
>X-WBNR-Posting-Host: 198.133.139.5
>From: examnotes <howlinghound@.nospam.nospam>
>Subject: Database Roles
>Date: Mon, 1 May 2006 11:47:02 -0700
>Lines: 7
>Message-ID: <DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.security
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.security:27213
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.security
>In SQL Server 2005, I want to create a database role and then create
>additional roles based on the earlier role (sort of an inheritance
approach
>to creating roles). However, I can't seem to find a way to make one role a
>member of another, share schema... Is this possible or advisable?
>--
>Michael Hockstein
>|||So, if I execute a statement such as:
GRANT CONTROL ON Role1 TO Role2
Go
then anything that Role1 had permissions to would be applied to Role2 as wel
l?
Michael Hockstein
"Wei Lu" wrote:
> Hi Michael,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: You want to
> assign a database role to be a member of another database role. If I
> misunderstood your concern, please feel free to let me know.
> You can not assign a database role to be a member of another database role
.
> If you want to grant the same permission of another database role and
> extend the permission, please grant the CONTROL permission to the database
> role for your additional database role.
> Thank you for taking the time to provide feedback on this product.
> We are very interested in your thoughts and opinions for improvements that
> we can make to provide the features and functionality you and your
> customers would like to see.
> To provide your feedback, please go the following website:
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> approach
>|||Hi Michael,
As I mentioned in the previous post, you can not grant the same permission
of a database role directly.
My suggestion is, you could check the sys.database_permissions catalog view
to see what the permissions does the original role grant and you could
grant the additional role the same permissions.
Grant the control permission to a database role will not grant the same
permission on database.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||"michael" <howlinghound@.nospam.nospam> wrote in message
news:DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com...
> In SQL Server 2005, I want to create a database role and then create
> additional roles based on the earlier role (sort of an inheritance
> approach
> to creating roles). However, I can't seem to find a way to make one role a
> member of another, share schema... Is this possible or advisable?
>
Not possible, but:
With the ability to grant on the database or schema level, the number of
indvidual grants necessary to implement security is typically much reduced.
You can add users to multiple roles, so the incremental rights can be
attached to an additional role.
David
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
--
Message posted via http://www.sqlmonster.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.sqlmonster.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>> If I set up a user with a database role of dbo, does that automatically
>> give
>[quoted text clipped - 3 lines]
>> go in and put an "X" in the execute permission checkbox next to the stored
>> procedure?
--
Message posted via http://www.sqlmonster.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via SQLMonster.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
--
Message posted via http://www.sqlmonster.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.sqlmonster.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>> If I set up a user with a database role of dbo, does that automatically
>> give
>[quoted text clipped - 3 lines]
>> go in and put an "X" in the execute permission checkbox next to the stored
>> procedure?
--
Message posted via http://www.sqlmonster.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via SQLMonster.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
Database Roles
Hi,
My understanding of db roles is that you don't have to explicitly set
permissions on objects as they role should give them this.
For example is I assign db_datareader to a user, then they automatically
have "Select" permissions on all tables
Is this right or am I going mad
SimonYes, assign a role to a user and the user will get the permissions defined
by the roles. Just like groups in any product. :-)
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> My understanding of db roles is that you don't have to explicitly set
> permissions on objects as they role should give them this.
> For example is I assign db_datareader to a user, then they automatically
> have "Select" permissions on all tables
> Is this right or am I going mad
> Simon
>|||Strange
I have assigned this user to the role, but was still getting Select
permissions denied errors. What else could be wrong?
Simon
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> Yes, assign a role to a user and the user will get the permissions defined
> by the roles. Just like groups in any product. :-)
> --
> Tibor Karaszi
>
> "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My understanding of db roles is that you don't have to explicitly set
> > permissions on objects as they role should give them this.
> >
> > For example is I assign db_datareader to a user, then they automatically
> > have "Select" permissions on all tables
> >
> > Is this right or am I going mad
> >
> > Simon
> >
> >
>|||Perhaps you have assigned DENY permissions somehow? Deny is always stronger.
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23MJ8qj5oDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Strange
> I have assigned this user to the role, but was still getting Select
> permissions denied errors. What else could be wrong?
> Simon
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> > Yes, assign a role to a user and the user will get the permissions
defined
> > by the roles. Just like groups in any product. :-)
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> > news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > My understanding of db roles is that you don't have to explicitly set
> > > permissions on objects as they role should give them this.
> > >
> > > For example is I assign db_datareader to a user, then they
automatically
> > > have "Select" permissions on all tables
> > >
> > > Is this right or am I going mad
> > >
> > > Simon
> > >
> > >
> >
> >
>
My understanding of db roles is that you don't have to explicitly set
permissions on objects as they role should give them this.
For example is I assign db_datareader to a user, then they automatically
have "Select" permissions on all tables
Is this right or am I going mad
SimonYes, assign a role to a user and the user will get the permissions defined
by the roles. Just like groups in any product. :-)
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> My understanding of db roles is that you don't have to explicitly set
> permissions on objects as they role should give them this.
> For example is I assign db_datareader to a user, then they automatically
> have "Select" permissions on all tables
> Is this right or am I going mad
> Simon
>|||Strange
I have assigned this user to the role, but was still getting Select
permissions denied errors. What else could be wrong?
Simon
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> Yes, assign a role to a user and the user will get the permissions defined
> by the roles. Just like groups in any product. :-)
> --
> Tibor Karaszi
>
> "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My understanding of db roles is that you don't have to explicitly set
> > permissions on objects as they role should give them this.
> >
> > For example is I assign db_datareader to a user, then they automatically
> > have "Select" permissions on all tables
> >
> > Is this right or am I going mad
> >
> > Simon
> >
> >
>|||Perhaps you have assigned DENY permissions somehow? Deny is always stronger.
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23MJ8qj5oDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Strange
> I have assigned this user to the role, but was still getting Select
> permissions denied errors. What else could be wrong?
> Simon
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> > Yes, assign a role to a user and the user will get the permissions
defined
> > by the roles. Just like groups in any product. :-)
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> > news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > My understanding of db roles is that you don't have to explicitly set
> > > permissions on objects as they role should give them this.
> > >
> > > For example is I assign db_datareader to a user, then they
automatically
> > > have "Select" permissions on all tables
> > >
> > > Is this right or am I going mad
> > >
> > > Simon
> > >
> > >
> >
> >
>
Database Role/User Query
Anyone have a tsql query that will give me a listing of database roles and their users already put together?try sp_helpuser with no arguments.
Database Role that allows execution of stored procedures?
We have a rule for developing database-driven applications that all
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.
No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_sc..._sql_tasks.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>
|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>
|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.
No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_sc..._sql_tasks.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>
|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>
|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>
Database Role that allows execution of stored procedures?
We have a rule for developing database-driven applications that all
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_s...e_sql_tasks.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_s...e_sql_tasks.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>
Database Role that allows execution of stored procedures?
We have a rule for developing database-driven applications that all
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>> We have a rule for developing database-driven applications that all
>> interaction with the database must be done through stored procedures i.e.
>> all selects, inserts, updates etc.
>> I am looking for simple ways to enforce & support this design principle -
>> and one would be if I could put the SQL login that the application uses
>> into a database role(s) that only allowed execution of stored procedure,
>> i.e. no direct access to tables or views. I know that the long way to do
>> this is to create my own role and grant it execute rights on each SP and
>> no rights to tables/views, but I was wondering if there was anything
>> already built into SQL Server.
>
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>> We have a rule for developing database-driven applications that all
>> interaction with the database must be done through stored procedures i.e.
>> all selects, inserts, updates etc.
>> I am looking for simple ways to enforce & support this design principle -
>> and one would be if I could put the SQL login that the application uses
>> into a database role(s) that only allowed execution of stored procedure,
>> i.e. no direct access to tables or views. I know that the long way to do
>> this is to create my own role and grant it execute rights on each SP and
>> no rights to tables/views, but I was wondering if there was anything
>> already built into SQL Server.
>
Database Role Security Permissions
Hi,
How can we determine permissions of a database role?
I could only find out how to determine user and login perms:
EXECUTE AS user = 'Omid' GO SELECT has_perms_by_name(db_name(), 'DATABASE', 'ANY') GO REVERT GOAny suggestions?I can't believe there is no reply after a day in MSDN forums. Anyway not to disappoint folks with the same problem, there is a very very stupid solution: Create a temp user(login) and add this user to the database role and then check the permissions and at last remove the user!|||Hey, sometimes we all have to take a break
Are you using 2005? If so, you can use the sys.database_permissions view. Here is a blog that I forgot that I wrote about this until I started doing some research for you
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1485.entry
This query gets table permissions in a database, with object names... Easy enough to expand for other types of objects:
select database_permissions.permission_name,
coalesce(objects.type_desc,database_permissions.class_desc)
+ case when objects.type_desc is not null and minor_id > 0 then '-COLUMN'
else '' end as object_type,
case database_permissions.class_desc
when 'SCHEMA' then schema_name(major_id)
when 'OBJECT_OR_COLUMN' then
case when minor_id = 0 then object_name(major_id)
else (select object_name(object_id) + '.'+ name
from sys.columns
where object_id = database_permissions.major_id
and column_id = database_permissions.minor_id) end
else 'other' end as object_name,
database_principals.name as database_principal,
database_permissions.state_desc as grant_state
from sys.database_permissions
join sys.database_principals
on database_permissions.grantee_principal_id = database_principals.principal_id
left join sys.objects --left because it is possible that it is a schema
on objects.object_id = database_permissions.major_id
where database_permissions.major_id > 0
and permission_name in ('SELECT','INSERT','UPDATE','DELETE')
order by object_name
I really appreciate it. Although I've already implemented the "stupid" solution, I'll surely change it to use the query as soon as possible.
Thanks again.
database role secureables not shows up
Hello!
After creating a new database role I have add some permissions. Now, when I want to view the role an the permissions the secureables page are empty.
This problem is only within the management studio. If I use Enterprise Manager I can see that the changes are taking effect.
Any hints?
Lutz
I see the same, I will open a bug on this.
If you would like to know whether the the permissions are granted to an object, right click the object and select ->Properties and view the permissions tab
Thanks,
Gops Dwarak
Database Role & Application Roles
In my environment we have all database roles..
Where we can setup application roles and can it will be more secure then
database roles.. will it be easy to manage,,Hi,
Application Roles:This is the best method for controlling user activities
regardless of the application used to communicate
with SQL Server
With the use of application roles you can restrict the users the usage of
Enterprise manager and Query Analyzer.
Say for your application to run you need give INSERT/DELETE and UPDATE
previlages to a user, if you
gave those previlages to user then he can login to Query analyzer and do any
thing on tables.
To overcome these you can assign all the previlages to a app role and enable
the app role inside the application.
App role will get enabled only by providing the right password, which is
defined inside the application. So even
if the user login using query analyzer he cant do any thing.
Thanks
Hari
SQL Server, MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:5A9F6EEA-F56F-4598-AEDC-E17374F80BEE@.microsoft.com...
> In my environment we have all database roles..
> Where we can setup application roles and can it will be more secure then
> database roles.. will it be easy to manage,,
Where we can setup application roles and can it will be more secure then
database roles.. will it be easy to manage,,Hi,
Application Roles:This is the best method for controlling user activities
regardless of the application used to communicate
with SQL Server
With the use of application roles you can restrict the users the usage of
Enterprise manager and Query Analyzer.
Say for your application to run you need give INSERT/DELETE and UPDATE
previlages to a user, if you
gave those previlages to user then he can login to Query analyzer and do any
thing on tables.
To overcome these you can assign all the previlages to a app role and enable
the app role inside the application.
App role will get enabled only by providing the right password, which is
defined inside the application. So even
if the user login using query analyzer he cant do any thing.
Thanks
Hari
SQL Server, MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:5A9F6EEA-F56F-4598-AEDC-E17374F80BEE@.microsoft.com...
> In my environment we have all database roles..
> Where we can setup application roles and can it will be more secure then
> database roles.. will it be easy to manage,,
Database Role & Application
What is the basic difference between these roles..
I know as per books but how it can be used in real life. We have all roles
defined as database roles.. where we can use app roleDave,
here are some good articles:
http://www.databasejournal.com/feat...cle.php/3363521
http://www.sqlteam.com/item.asp?ItemID=864
http://vyaskn.tripod.com/sql_server...t_practices.htm
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1D3ECF9A-E3C5-403A-8F26-96733B267561@.microsoft.com...
> What is the basic difference between these roles..
> I know as per books but how it can be used in real life. We have all roles
> defined as database roles.. where we can use app role
I know as per books but how it can be used in real life. We have all roles
defined as database roles.. where we can use app roleDave,
here are some good articles:
http://www.databasejournal.com/feat...cle.php/3363521
http://www.sqlteam.com/item.asp?ItemID=864
http://vyaskn.tripod.com/sql_server...t_practices.htm
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1D3ECF9A-E3C5-403A-8F26-96733B267561@.microsoft.com...
> What is the basic difference between these roles..
> I know as per books but how it can be used in real life. We have all roles
> defined as database roles.. where we can use app role
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 ?
ThanksEveryone 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:
>>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
>.
>|||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:
>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:
>>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
>>.
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 ?
ThanksEveryone 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:
>>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
>.
>|||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:
>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:
>>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
>>.
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
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
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 ?
ThanksEveryone 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
>
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 ?
ThanksEveryone 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
>
DataBase Role
Hi
I want to add my database role to a server fixed role like other logins but
I can't.
Please help me.
Thanks
Mehdi> I want to add my database role to a server fixed role like other logins
> but
> I can't.
You can add only logins to fixed server roles.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||> I want to add my database role to a server fixed role like other logins
> but
> I can't.
Logins are server-level so you can add logins to server roles. Since
database roles are only recognized only within the scope of that database,
database roles can't be added to server roles.
What are your security requirements? Perhaps you can grant the desired
permissions directly to the database role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:46F5EB48-DBE0-43EC-8D3C-501E02F496EA@.microsoft.com...
> Hi
> I want to add my database role to a server fixed role like other logins
> but
> I can't.
> Please help me.
> Thanks
> Mehdi
I want to add my database role to a server fixed role like other logins but
I can't.
Please help me.
Thanks
Mehdi> I want to add my database role to a server fixed role like other logins
> but
> I can't.
You can add only logins to fixed server roles.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||> I want to add my database role to a server fixed role like other logins
> but
> I can't.
Logins are server-level so you can add logins to server roles. Since
database roles are only recognized only within the scope of that database,
database roles can't be added to server roles.
What are your security requirements? Perhaps you can grant the desired
permissions directly to the database role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:46F5EB48-DBE0-43EC-8D3C-501E02F496EA@.microsoft.com...
> Hi
> I want to add my database role to a server fixed role like other logins
> but
> I can't.
> Please help me.
> Thanks
> Mehdi
Subscribe to:
Posts (Atom)