Migrating to SQL 2005: provide

Showing posts with label provide. Show all posts

Friday, March 23, 2012

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|||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:
> > 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 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:
> 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:
> > > 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
> >
> >
> >|||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...
>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:
>> 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:
>> > > 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
>> >
>> >
>> >|||The DSN points to the proper server/database and the Test Connection says the
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...
> >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:
> >
> >> 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:
> >>
> >> > > 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
> >> >
> >> >
> >> >
>
>|||Since the upsize works as a sysadmin role member, it certainly seems the
problem is permission related. However, I can't think of anything the
wizard might need beyond dbo as long as you aren't creating a new database.
Try running a SQL Profiler trace during the upsize to identify the problem
statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:A9C995B5-9488-4D7C-B19A-39EF99EC045A@.microsoft.com...
> The DSN points to the proper server/database and the Test Connection says
> the
> 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...
>> >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:
>> >
>> >> 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:
>> >>
>> >> > > 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 ran a trace and I wasn't able to interpret any of the output as pointing to
the inability to upsize. I'd be happy to provide any trace info for your
perusal.
I made the test user a member of the sysadmin server role and the upsize ran
ok. The contractor we are using needs upsize functionality to a db but I
don't want to make him a sysamdin on the server in order to give him this
functionality.
Nothing I have been able to find online, in the $60 books I have or anywhere
else has indicated a need for sysadmin rights to do an upsize to a db but my
real world experience is looking this way.
I'm thinking of spending $250 or whatever of my company's money for a phone
call to Microsoft to get an answer.
Thanks for all your help so far, any other info appreciated.
"Dan Guzman" wrote:
> Since the upsize works as a sysadmin role member, it certainly seems the
> problem is permission related. However, I can't think of anything the
> wizard might need beyond dbo as long as you aren't creating a new database.
> Try running a SQL Profiler trace during the upsize to identify the problem
> statement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "pdx" <pdx@.discussions.microsoft.com> wrote in message
> news:A9C995B5-9488-4D7C-B19A-39EF99EC045A@.microsoft.com...
> > The DSN points to the proper server/database and the Test Connection says
> > the
> > 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...
> >> >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:
> >> >
> >> >> 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:
> >> >>
> >> >> > > 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
> >> >> >
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>|||My hope is that the trace will help identify the problem SQL statements or
at least how far the process got before the problem. Did you trace batch
completed and errors/exceptions events? Do you see CREATE TABLE statements,
etc.?
I tried a simple test with Access 2003 and had no problems upsizing a table
when logged in as the dbo (but non-sysadmin role member). You might try
connecting as the problem user using Query Analyzer and verifying the
security context with 'SELECT USER'. This should return 'dbo' in the user
database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:1183BA89-3D6E-42C7-A796-F1E1D51784BC@.microsoft.com...
>I ran a trace and I wasn't able to interpret any of the output as pointing
>to
> the inability to upsize. I'd be happy to provide any trace info for your
> perusal.
> I made the test user a member of the sysadmin server role and the upsize
> ran
> ok. The contractor we are using needs upsize functionality to a db but I
> don't want to make him a sysamdin on the server in order to give him this
> functionality.
> Nothing I have been able to find online, in the $60 books I have or
> anywhere
> else has indicated a need for sysadmin rights to do an upsize to a db but
> my
> real world experience is looking this way.
> I'm thinking of spending $250 or whatever of my company's money for a
> phone
> call to Microsoft to get an answer.
> Thanks for all your help so far, any other info appreciated.
> "Dan Guzman" wrote:
>> Since the upsize works as a sysadmin role member, it certainly seems the
>> problem is permission related. However, I can't think of anything the
>> wizard might need beyond dbo as long as you aren't creating a new
>> database.
>> Try running a SQL Profiler trace during the upsize to identify the
>> problem
>> statement.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "pdx" <pdx@.discussions.microsoft.com> wrote in message
>> news:A9C995B5-9488-4D7C-B19A-39EF99EC045A@.microsoft.com...
>> > The DSN points to the proper server/database and the Test Connection
>> > says
>> > the
>> > 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...
>> >> >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:
>> >> >
>> >> >> 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:
>> >> >>
>> >> >> > > 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
>> >> >> >
>> >> >> >
>> >> >> >
>> >>
>> >>
>> >>
>>|||Its interesting that you were able to do the upsize as a non-sysadmin. Since
it didn't work for me I had to open a ticket with Microsoft (which is being
refunded).
The techs conclusion was that this is a bug and it doesn't work as
advertised (i.e. as a db_owner), see the conclusion below:
**
"further researched on the issue and found the following. Access
Documentation mentions that the user that is running
The â'Upsizing Wizard â' need not have the sysadmin privilege if the database
is already present see the links below.
http://support.microsoft.com/kb/244309/en-us
http://office.microsoft.com/en-ca/assistance/HP052741431033.aspx
But in your case we have seen that we require the sysadmin right to change
the configuration option â'show advanced optionsâ' to 1
If it is set to 0 in SQL Server
Changing the â'show advanced optionsâ' to 1before running the wizard resolves
the issue."
**
Running the following sp on my server allowed the upsizing to work:
sp_configure 'show advanced option',1
go
reconfigure
go
**
Thanks
"Dan Guzman" wrote:
> My hope is that the trace will help identify the problem SQL statements or
> at least how far the process got before the problem. Did you trace batch
> completed and errors/exceptions events? Do you see CREATE TABLE statements,
> etc.?
> I tried a simple test with Access 2003 and had no problems upsizing a table
> when logged in as the dbo (but non-sysadmin role member). You might try
> connecting as the problem user using Query Analyzer and verifying the
> security context with 'SELECT USER'. This should return 'dbo' in the user
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "pdx" <pdx@.discussions.microsoft.com> wrote in message
> news:1183BA89-3D6E-42C7-A796-F1E1D51784BC@.microsoft.com...
> >I ran a trace and I wasn't able to interpret any of the output as pointing
> >to
> > the inability to upsize. I'd be happy to provide any trace info for your
> > perusal.
> >
> > I made the test user a member of the sysadmin server role and the upsize
> > ran
> > ok. The contractor we are using needs upsize functionality to a db but I
> > don't want to make him a sysamdin on the server in order to give him this
> > functionality.
> >
> > Nothing I have been able to find online, in the $60 books I have or
> > anywhere
> > else has indicated a need for sysadmin rights to do an upsize to a db but
> > my
> > real world experience is looking this way.
> > I'm thinking of spending $250 or whatever of my company's money for a
> > phone
> > call to Microsoft to get an answer.
> >
> > Thanks for all your help so far, any other info appreciated.
> >
> > "Dan Guzman" wrote:
> >
> >> Since the upsize works as a sysadmin role member, it certainly seems the
> >> problem is permission related. However, I can't think of anything the
> >> wizard might need beyond dbo as long as you aren't creating a new
> >> database.
> >> Try running a SQL Profiler trace during the upsize to identify the
> >> problem
> >> statement.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "pdx" <pdx@.discussions.microsoft.com> wrote in message
> >> news:A9C995B5-9488-4D7C-B19A-39EF99EC045A@.microsoft.com...
> >> > The DSN points to the proper server/database and the Test Connection
> >> > says
> >> > the
> >> > 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...
> >> >> >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:
> >> >> >
> >> >> >> 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:
> >> >> >>
> >> >> >> > > 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
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||> Its interesting that you were able to do the upsize as a non-sysadmin.
> snip
> Running the following sp on my server allowed the upsizing to work:
> sp_configure 'show advanced option',1
> go
> reconfigure
> go
I always run with 'show advanced option'. That explains why I had no
problems, even as a sysadmin.
Thanks for sharing. Hopefully this will help someone down the road.
--
Dan Guzman
SQL Server MVP
"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:5677662C-10E3-4909-8E7E-42EA4243978B@.microsoft.com...
> Its interesting that you were able to do the upsize as a non-sysadmin.
> Since
> it didn't work for me I had to open a ticket with Microsoft (which is
> being
> refunded).
> The techs conclusion was that this is a bug and it doesn't work as
> advertised (i.e. as a db_owner), see the conclusion below:
> **
> "further researched on the issue and found the following. Access
> Documentation mentions that the user that is running
> The "Upsizing Wizard " need not have the sysadmin privilege if the
> database
> is already present see the links below.
> http://support.microsoft.com/kb/244309/en-us
> http://office.microsoft.com/en-ca/assistance/HP052741431033.aspx
> But in your case we have seen that we require the sysadmin right to change
> the configuration option 'show advanced options' to 1
> If it is set to 0 in SQL Server
> Changing the 'show advanced options' to 1before running the wizard
> resolves
> the issue."
> **
> Running the following sp on my server allowed the upsizing to work:
> sp_configure 'show advanced option',1
> go
> reconfigure
> go
> **
> Thanks
>
>
>
> "Dan Guzman" wrote:
>> My hope is that the trace will help identify the problem SQL statements
>> or
>> at least how far the process got before the problem. Did you trace batch
>> completed and errors/exceptions events? Do you see CREATE TABLE
>> statements,
>> etc.?
>> I tried a simple test with Access 2003 and had no problems upsizing a
>> table
>> when logged in as the dbo (but non-sysadmin role member). You might
>> try
>> connecting as the problem user using Query Analyzer and verifying the
>> security context with 'SELECT USER'. This should return 'dbo' in the
>> user
>> database.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "pdx" <pdx@.discussions.microsoft.com> wrote in message
>> news:1183BA89-3D6E-42C7-A796-F1E1D51784BC@.microsoft.com...
>> >I ran a trace and I wasn't able to interpret any of the output as
>> >pointing
>> >to
>> > the inability to upsize. I'd be happy to provide any trace info for
>> > your
>> > perusal.
>> >
>> > I made the test user a member of the sysadmin server role and the
>> > upsize
>> > ran
>> > ok. The contractor we are using needs upsize functionality to a db but
>> > I
>> > don't want to make him a sysamdin on the server in order to give him
>> > this
>> > functionality.
>> >
>> > Nothing I have been able to find online, in the $60 books I have or
>> > anywhere
>> > else has indicated a need for sysadmin rights to do an upsize to a db
>> > but
>> > my
>> > real world experience is looking this way.
>> > I'm thinking of spending $250 or whatever of my company's money for a
>> > phone
>> > call to Microsoft to get an answer.
>> >
>> > Thanks for all your help so far, any other info appreciated.
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> Since the upsize works as a sysadmin role member, it certainly seems
>> >> the
>> >> problem is permission related. However, I can't think of anything the
>> >> wizard might need beyond dbo as long as you aren't creating a new
>> >> database.
>> >> Try running a SQL Profiler trace during the upsize to identify the
>> >> problem
>> >> statement.
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "pdx" <pdx@.discussions.microsoft.com> wrote in message
>> >> news:A9C995B5-9488-4D7C-B19A-39EF99EC045A@.microsoft.com...
>> >> > The DSN points to the proper server/database and the Test Connection
>> >> > says
>> >> > the
>> >> > 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...
>> >> >> >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:
>> >> >> >
>> >> >> >> 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:
>> >> >> >>
>> >> >> >> > > 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
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Friday, March 23, 2012

Minimum perms for a user work with a db?

Minimum perms for a user work with a db?

Migrating to SQL 2005

Blog Archive

About Me