I granted CREATE TABLE to the signed-in user, but the export fails. With sa
account it would work.
Which permissions are missing?
ThanksIf the database exists, the user will still need create
database permissions as well as permissions to create
tables, views, stored procedures, triggers, etc depending on
what objects you have in your Access database and what
options you select. If the database does not exist, the user
needs permissions to select from system tables in master.
Just granting Create table won't be enough.
-Sue
On Mon, 7 Mar 2005 05:59:04 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:
>I granted CREATE TABLE to the signed-in user, but the export fails. With sa
>account it would work.
>Which permissions are missing?
>Thanks|||Hi Sue
Thanks first for your help.
The database exists. I droped the user for recreation in master, added the
role db_owner and granted create database in master and new db.
Still don't work. Any idea? I import just tables, index and relations with
DRI.|||Go into master and execute:
grant create database to YourUser
Then go into the database that will be used for upsizing and
make the user the owner of the database by executing:
sp_changedbowner 'YourUser'
-Sue
On Tue, 8 Mar 2005 06:41:02 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:
>Hi Sue
>Thanks first for your help.
>The database exists. I droped the user for recreation in master, added the
>role db_owner and granted create database in master and new db.
>Still don't work. Any idea? I import just tables, index and relations with
>DRI.
>|||Hello Sue
> Go into master and execute:
> grant create database to YourUser
Done. What I do in a scratch database is:
use master
CREATE DATABASE myDB
go
EXEC sp_addlogin 'usr', 'pwd'
EXEC sp_grantdbaccess 'usr'
EXEC sp_addrolemember 'db_owner', 'usr'
grant CREATE DATABASE to usr
go
use myDB
EXEC sp_grantdbaccess 'usr'
exec sp_addrolemember 'db_owner', 'usr'
go
> Then go into the database that will be used for upsizing and
> make the user the owner of the database by executing:
> sp_changedbowner 'YourUser'
When I execute this statement, I get the error that it is already owner of
db. Maybe because of sp_addrolemember 'db_owner'?|||Remove the user from the database - you don't want the
account being a user in the database when assigning the
account as the owner of the database. Then execute
sp_changedbowner.
-Sue
On Tue, 8 Mar 2005 23:43:04 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:
>Hello Sue
>
>Done. What I do in a scratch database is:
> use master
> CREATE DATABASE myDB
> go
> EXEC sp_addlogin 'usr', 'pwd'
> EXEC sp_grantdbaccess 'usr'
> EXEC sp_addrolemember 'db_owner', 'usr'
> grant CREATE DATABASE to usr
> go
> use myDB
> EXEC sp_grantdbaccess 'usr'
> exec sp_addrolemember 'db_owner', 'usr'
> go
>
>When I execute this statement, I get the error that it is already owner of
>db. Maybe because of sp_addrolemember 'db_owner'?
>|||Sorry Sue
Don't understand anything. If I remove the user it can't be the owner
(because it doesn't exist). If I remove the dbaccess, I can't login anymore.
What do you mean?
Could you send me a script from scratch database like:
use master
CREATE DATABASE myDB
go
EXEC sp_addlogin 'usr', 'pwd'
EXEC sp_grantdbaccess 'usr'
EXEC sp_addrolemember 'db_owner', 'usr'
grant CREATE DATABASE to usr
go
use myDB
EXEC sp_grantdbaccess 'usr'
exec sp_addrolemember 'db_owner', 'usr'
go
Thanks|||This is the part you don't want to use. This adds the
account as a user in the database. You don't want the
account added as a user in the database. Don't execute this
part at all. Instead, make the user the database owner NOT a
member of the db_owner role.
So instead of this part, execute:
use myDB
exec sp_changedbowner 'usr'
There is a difference between being the database owner and
being a member of the db_owner role.
You script has another reference where you are adding the
usr to the db_owners role for master and you don't want
that.
Your entire script would read like:
use master
go
CREATE DATABASE myDB
go
EXEC sp_addlogin 'usr', 'pwd'
EXEC sp_grantdbaccess 'usr'
grant CREATE DATABASE to usr
go
use myDB
go
EXEC sp_changedbowner 'usr'
go
I just ran it and upsized a database logging in as usr and
using myDB as the destination database, upsizing tables,
indexes and DRI.
-Sue
On Fri, 11 Mar 2005 00:29:02 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:
>use myDB
> EXEC sp_grantdbaccess 'usr'
> exec sp_addrolemember 'db_owner', 'usr'
> go|||Hi Sue
Thanks for you replies and patience. Unfortunately your script still doesn't
work. The tables in Access are skipped...|||Don't know what else to tell you - the permissions keep working for me just
fine. I just tried in another different environment (so we are up to three
now - different server, different PCs with Access DBs) and it worked fine.
I have upsized different databases 6 times now with a user with create
database permissions and the owner of the destination database for the
upsized objects. Upsized tables, indexes, DRI. Just followed the origninal
steps:
Go into master and execute:
grant create database to YourUser
Then go into the database that will be used for upsizing and
make the user the owner of the database by executing:
sp_changedbowner 'YourUser'
If the tables are skipped with no errors then basically nothing was upsized
as the indexes and DRI couldn't be done. You will need to track down where
the error is and provide an easily repro scenario as I am unable to reproduc
e
your problems.
-Sue
