Minimum SQL Server permissions needed

[Rob]

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.

 

[Sylvain Lafontaine]

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]

For the connection object, the most simple is to use the one who is already
available:

CurrentProject.Connection.Execute ("Your sql statement here")

 

[Rob]

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 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.

Thank you

 

[Erland Sommarskog]

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, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[riccardo]

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.