Minimum SQL Server permissions needed

I have some Access .adp VBA code (below) that deletes a SQL Server 2005 tabl
e
and recreates it.
DoCmd.DeleteObject acTable, "dbo.tblTemp"
DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
I would like to create a role that has the minimum permissions necessary to
do this. Any advice?
If I don't SELECT control to the two tables, Access doesn't seem to see
them. If I don't grant CONTROL, Access doesn't seem to be able to drop the
tables. Yet, after the tblTemp is recreated in the CopyObject line of code,
Access doesn't see it again as the role doesn't have that SELECT or CONTROL
permissions granted to it anymore.
Another problem which seems like overkill was that I had to grant CREATE
TABLE to the role.
Also, If I don't grant control to the role dbo, Access squawks about not
having permissions to dbo.
Your help is appreciated.Not sure about your case - as you don't mention how you are trying to access
these tables after their creations but did you look into the possibility of
creating your tables in the tempdb database?
(I don't even know if this will work from DoCmd.*; however, using a SP or
the ADO connection or the command objects would probably be a much better
idea than using DoCmd.* even if the DoCmd.* are working).
For example:
Set NoCount ON
create table Tempdb..Members (IdMember int Identity (1,1) primary key,
firstname
varchar(50) collate database_default)
insert into Tempdb..Members (firstname) values ('deny')
insert into Tempdb..Members (firstname) values ('ben')
select M.* from Tempdb..Members as M
drop table Tempdb..Members
(the collate database_default statement is there in case the default
collation for the tempdb database would be different from the default
collation of the current database. If this your case, don't forget the
collate database_default statement and don't use a use Tempdb
statement; otherwise the collation used will be the one defined for the
tempdb database. Of course, if both default collations are the same then
you don't have to fiddle with this.)
In my opinion, granting Control and Create table permission on an account is
pretty much giving away all security; so if possible, it would be a much
better idea to use the tempdb database; as this database has been created
exactly for that purpose.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>I have some Access .adp VBA code (below) that deletes a SQL Server 2005
>table
> and recreates it.
> DoCmd.DeleteObject acTable, "dbo.tblTemp"
> DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
> I would like to create a role that has the minimum permissions necessary
> to
> do this. Any advice?
> If I don't SELECT control to the two tables, Access doesn't seem to see
> them. If I don't grant CONTROL, Access doesn't seem to be able to drop
> the
> tables. Yet, after the tblTemp is recreated in the CopyObject line of
> code,
> Access doesn't see it again as the role doesn't have that SELECT or
> CONTROL
> permissions granted to it anymore.
> Another problem which seems like overkill was that I had to grant CREATE
> TABLE to the role.
> Also, If I don't grant control to the role dbo, Access squawks about not
> having permissions to dbo.
> Your help is appreciated.|||For the connection object, the most simple is to use the one who is already
available:
CurrentProject.Connection.Execute ("Your sql statement here")
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>|||Sylvain,
This is an older app that I've inherited. It currently runs with sa rights
(not good), so I was trying to create a new user & role with a limited set o
f
rights that could still allow the Access app to do what it needs. I was
hoping to avoid overly changing the MS Access code, but that may not be
possible. Your suggestion of ditching the DoCmd for executing a stored proc
though is a good one. I'll pursue this.
Thank you
"Sylvain Lafontaine" wrote:
[vbcol=seagreen]
> Not sure about your case - as you don't mention how you are trying to acce
ss
> these tables after their creations but did you look into the possibility o
f
> creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the ? collate database_default ? statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the ?
> collate database_default ? statement and don't use a ? use Tempdb ?
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
is
> pretty much giving away all security; so if possible, it would be a much
> better idea to use the tempdb database; as this database has been created
> exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...|||Rob (Rob@.discussions.microsoft.com) writes:
> This is an older app that I've inherited. It currently runs with sa
> rights (not good), so I was trying to create a new user & role with a
> limited set of rights that could still allow the Access app to do what
> it needs. I was hoping to avoid overly changing the MS Access code, but
> that may not be possible. Your suggestion of ditching the DoCmd for
> executing a stored proc though is a good one. I'll pursue this.
Yes, putting this in a stored proceedure is the only way out. But Sylvain
did not tell the full story. For this to work you need to sign the procedure
with a certificate, and create a user fot the certificate and grant that
user the required rights.
I describe this in detail in this article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> ha
scritto nel messaggio news:#aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>