Minimum perms for a user work with a db?

I have a user(s) who I need to provide the ability to work with a SQL 2005
db. The db will be pre-setup for the user.
I want the user to be able to upsize Access dbs to SQL. Also the ability to
use DTS/SSIS would be useful. In actuality what I am looking for is the the
ability to allow the user to do anything they want with their db but not the
ability to access other dbs on the server.
I set up a db and gave a test user db_owner access to the db but an upsize
file with the error "table was skipped or export failed" on all tables. This
same db was upsized ok as an admin user.
The user will be accessing the SQL server through the SQL Mgmt Studio, etc
running in a Terminal Server session.
How do I do what I am looking to do?
Thanks> I set up a db and gave a test user db_owner access to the db but an upsize
> file with the error "table was skipped or export failed" on all tables.
> This
> same db was upsized ok as an admin user.
db_owner should provide all the permissions needed for the upsize but it
looks like the tool may require the user to be 'dbo'. Try changing the
database owner to your test user login:
SQL 2000:
USE MyDatabase
EXEC sp_dropuser 'TestUser'
EXEC sp_changedbowner 'TestUser'
SQL 2005:
USE MyDatabase
DROP USER TestUser
ALTER AUTHORIZATION ON
DATABASE::MyDatabase TO [TestUser]
Hope this helps.
Dan Guzman
SQL Server MVP
"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:9888DB59-8EC0-4200-BA1D-1C2836B4F06C@.microsoft.com...
>I have a user(s) who I need to provide the ability to work with a SQL 2005
> db. The db will be pre-setup for the user.
> I want the user to be able to upsize Access dbs to SQL. Also the ability
> to
> use DTS/SSIS would be useful. In actuality what I am looking for is the
> the
> ability to allow the user to do anything they want with their db but not
> the
> ability to access other dbs on the server.
> I set up a db and gave a test user db_owner access to the db but an upsize
> file with the error "table was skipped or export failed" on all tables.
> This
> same db was upsized ok as an admin user.
> The user will be accessing the SQL server through the SQL Mgmt Studio, etc
> running in a Terminal Server session.
> How do I do what I am looking to do?
> Thanks|||> I set up a db and gave a test user db_owner access to the db but an upsize
> file with the error "table was skipped or export failed" on all tables.
> This
> same db was upsized ok as an admin user.
db_owner should provide all the permissions needed for the upsize but it
looks like the tool may require the user to be 'dbo'. Try changing the
database owner to your test user login:
SQL 2000:
USE MyDatabase
EXEC sp_dropuser 'TestUser'
EXEC sp_changedbowner 'TestUser'
SQL 2005:
USE MyDatabase
DROP USER TestUser
ALTER AUTHORIZATION ON
DATABASE::MyDatabase TO [TestUser]
Hope this helps.
Dan Guzman
SQL Server MVP
"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:9888DB59-8EC0-4200-BA1D-1C2836B4F06C@.microsoft.com...
>I have a user(s) who I need to provide the ability to work with a SQL 2005
> db. The db will be pre-setup for the user.
> I want the user to be able to upsize Access dbs to SQL. Also the ability
> to
> use DTS/SSIS would be useful. In actuality what I am looking for is the
> the
> ability to allow the user to do anything they want with their db but not
> the
> ability to access other dbs on the server.
> I set up a db and gave a test user db_owner access to the db but an upsize
> file with the error "table was skipped or export failed" on all tables.
> This
> same db was upsized ok as an admin user.
> The user will be accessing the SQL server through the SQL Mgmt Studio, etc
> running in a Terminal Server session.
> How do I do what I am looking to do?
> Thanks|||Thanks for the reply.
This is a SQL 2005 machine:
The db in question is TestDb1 and the user I want to provide dbo to the db
is Test100. When I ran the following:
USE TestDb1
DROP USER Test100
ALTER AUTHORIZATION ON
DATABASE::TestDb1 TO Test100
I recv the following error:
Msg 15151, Level 16, State 1, Line 2
Cannot drop the user 'test100', because it does not exist or you do not have
permission.
Msg 15110, Level 16, State 1, Line 3
The proposed new database owner is already a user or aliased in the database
.
**
If I run the following:
ALTER AUTHORIZATION ON DATABASE::TestDb1 TO Test100
I recv the following:
Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'TestUser', because it does not exist or you do
not have permission.
**
I tried a few things such as appending "<mydomain>\" to the username,
changing the focus in Query Analyzer from the db in question to master, and
changing the user being dropped to the current owner. Nothing worked.
Any idea what I'm doing wrong.
Thanks
"Dan Guzman" wrote:

> db_owner should provide all the permissions needed for the upsize but it
> looks like the tool may require the user to be 'dbo'. Try changing the
> database owner to your test user login:
> SQL 2000:
> USE MyDatabase
> EXEC sp_dropuser 'TestUser'
> EXEC sp_changedbowner 'TestUser'
> SQL 2005:
> USE MyDatabase
> DROP USER TestUser
> ALTER AUTHORIZATION ON
> DATABASE::MyDatabase TO [TestUser]
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "pdx" <pdx@.discussions.microsoft.com> wrote in message
> news:9888DB59-8EC0-4200-BA1D-1C2836B4F06C@.microsoft.com...
>
>|||I have now successfully given the Test100 account ownership of the TestDb1
db. However, I still get error:
"the table was skipped or export failed" on all tables when trying to upsize
an Access db to the SQL db.
Thanks,
"pdx" wrote:
[vbcol=seagreen]
> Thanks for the reply.
> This is a SQL 2005 machine:
> The db in question is TestDb1 and the user I want to provide dbo to the db
> is Test100. When I ran the following:
> USE TestDb1
> DROP USER Test100
> ALTER AUTHORIZATION ON
> DATABASE::TestDb1 TO Test100
> I recv the following error:
> Msg 15151, Level 16, State 1, Line 2
> Cannot drop the user 'test100', because it does not exist or you do not ha
ve
> permission.
> Msg 15110, Level 16, State 1, Line 3
> The proposed new database owner is already a user or aliased in the databa
se.
> **
> If I run the following:
> ALTER AUTHORIZATION ON DATABASE::TestDb1 TO Test100
> I recv the following:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the principal 'TestUser', because it does not exist or you do
> not have permission.
> **
> I tried a few things such as appending "<mydomain>\" to the username,
> changing the focus in Query Analyzer from the db in question to master, an
d
> changing the user being dropped to the current owner. Nothing worked.
> Any idea what I'm doing wrong.
> Thanks
>
> "Dan Guzman" wrote:
>|||Thanks for the reply.
This is a SQL 2005 machine:
The db in question is TestDb1 and the user I want to provide dbo to the db
is Test100. When I ran the following:
USE TestDb1
DROP USER Test100
ALTER AUTHORIZATION ON
DATABASE::TestDb1 TO Test100
I recv the following error:
Msg 15151, Level 16, State 1, Line 2
Cannot drop the user 'test100', because it does not exist or you do not have
permission.
Msg 15110, Level 16, State 1, Line 3
The proposed new database owner is already a user or aliased in the database
.
**
If I run the following:
ALTER AUTHORIZATION ON DATABASE::TestDb1 TO Test100
I recv the following:
Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'TestUser', because it does not exist or you do
not have permission.
**
I tried a few things such as appending "<mydomain>\" to the username,
changing the focus in Query Analyzer from the db in question to master, and
changing the user being dropped to the current owner. Nothing worked.
Any idea what I'm doing wrong.
Thanks
"Dan Guzman" wrote:

> db_owner should provide all the permissions needed for the upsize but it
> looks like the tool may require the user to be 'dbo'. Try changing the
> database owner to your test user login:
> SQL 2000:
> USE MyDatabase
> EXEC sp_dropuser 'TestUser'
> EXEC sp_changedbowner 'TestUser'
> SQL 2005:
> USE MyDatabase
> DROP USER TestUser
> ALTER AUTHORIZATION ON
> DATABASE::MyDatabase TO [TestUser]
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "pdx" <pdx@.discussions.microsoft.com> wrote in message
> news:9888DB59-8EC0-4200-BA1D-1C2836B4F06C@.microsoft.com...
>
>|||I have now successfully given the Test100 account ownership of the TestDb1
db. However, I still get error:
"the table was skipped or export failed" on all tables when trying to upsize
an Access db to the SQL db.
Thanks,
"pdx" wrote:
[vbcol=seagreen]
> Thanks for the reply.
> This is a SQL 2005 machine:
> The db in question is TestDb1 and the user I want to provide dbo to the db
> is Test100. When I ran the following:
> USE TestDb1
> DROP USER Test100
> ALTER AUTHORIZATION ON
> DATABASE::TestDb1 TO Test100
> I recv the following error:
> Msg 15151, Level 16, State 1, Line 2
> Cannot drop the user 'test100', because it does not exist or you do not ha
ve
> permission.
> Msg 15110, Level 16, State 1, Line 3
> The proposed new database owner is already a user or aliased in the databa
se.
> **
> If I run the following:
> ALTER AUTHORIZATION ON DATABASE::TestDb1 TO Test100
> I recv the following:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the principal 'TestUser', because it does not exist or you do
> not have permission.
> **
> I tried a few things such as appending "<mydomain>\" to the username,
> changing the focus in Query Analyzer from the db in question to master, an
d
> changing the user being dropped to the current owner. Nothing worked.
> Any idea what I'm doing wrong.
> Thanks
>
> "Dan Guzman" wrote:
>|||Are you sure the ODBC datasource is configured to use the correct
server/database and that the 'use existing database' option was checked?
Unlike a sysadmin role member, the 'dbo' will not have CREATE DATABASE
permissions. Of course, CREATE DATABASE shouldn't be needed if you are
using an existing database.
Hope this helps.
Dan Guzman
SQL Server MVP
"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:94B77825-DEB9-448E-9E8E-D66E7B53535E@.microsoft.com...[vbcol=seagreen]
>I have now successfully given the Test100 account ownership of the TestDb1
> db. However, I still get error:
> "the table was skipped or export failed" on all tables when trying to
> upsize
> an Access db to the SQL db.
> Thanks,
> "pdx" wrote:
>|||Are you sure the ODBC datasource is configured to use the correct
server/database and that the 'use existing database' option was checked?
Unlike a sysadmin role member, the 'dbo' will not have CREATE DATABASE
permissions. Of course, CREATE DATABASE shouldn't be needed if you are
using an existing database.
Hope this helps.
Dan Guzman
SQL Server MVP
"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:94B77825-DEB9-448E-9E8E-D66E7B53535E@.microsoft.com...[vbcol=seagreen]
>I have now successfully given the Test100 account ownership of the TestDb1
> db. However, I still get error:
> "the table was skipped or export failed" on all tables when trying to
> upsize
> an Access db to the SQL db.
> Thanks,
> "pdx" wrote:
>|||The DSN points to the proper server/database and the Test Connection says th
e
connection is ok.
"Use existing database" is checked but the upsize still fails with the same
error.
This is a lot more difficult than it should be...any other possibilities
that might be causing the problem?
"Dan Guzman" wrote:

> Are you sure the ODBC datasource is configured to use the correct
> server/database and that the 'use existing database' option was checked?
> Unlike a sysadmin role member, the 'dbo' will not have CREATE DATABASE
> permissions. Of course, CREATE DATABASE shouldn't be needed if you are
> using an existing database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "pdx" <pdx@.discussions.microsoft.com> wrote in message
> news:94B77825-DEB9-448E-9E8E-D66E7B53535E@.microsoft.com...
>
>