Rights for Access2000 Upsizing-Wizard

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

Right way to retrieve data from sql-server

Last night I face performace problems in web site. I got like 100 000 users and site fails because of sql-connections.

Currently I'm making a new connection for every query, and my code looks like this:

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataSetDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)Dim sqlDataSetAs New DataSetTry sqlConnection.Open()Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommand myDataAdapter.Fill(sqlDataSet)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return sqlDataSetEnd Function

I was wondering would it be better to save an instance from connection object to memory and use that same connection for all querys?

No. SQL connection pooling is automatic.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp

Which version of SQL are you running?

As a by note, a SQLAdapter will open its connection if it is closed, and if it did open it, close it immediately after it finishes.

The only real improvement above would be the use of a SQLDataReader if applicable, and if not, the instantiation of the SQLDataAdapter, and assignment of its command, prior to opening the connection.

|||

I do not think that saving connection will help you to much because you use connection pooling anyway
try to modify your code to look like this:

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataSetDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)

Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommandDim sqlDataSetAs New DataSetTry sqlConnection.Open() myDataAdapter.Fill(sqlDataSet)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return sqlDataSetEnd Function

So now your connection should be use for a little shorter period of time and it should allow other web user to access data,
you can also try to optimize your query to be processed in shortest amount of time. Another question is:

 Do you really need to return dataset? maybe returning table will be more efficient (less resources to use)?

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataTableDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)
Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommandDim ResultTableAs New DataTableTry sqlConnection.Open() myDataAdapter.Fill(ResultTable)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return ResultTableEnd Function
 
You can also try to use different way for reading your data DataReader?
Thanks

|||

But sometimes is safe to open and close connection by hands in case you would like to use the same connection object for multiple database access, because adapter sometimes does not close connection and reader inside it if it fails so you can have problem if you would like to use the same connection object after adapter. But if you use connection object for one call you can allow adapter to take care about opening/closing connection.

Thanks

|||

Thanks you!

Automatic pooling was new to me, I'm such a newbie :-)

Thanks

re-use rowguid, replication fails?

My ROWGUID columns are also my primary keys in many cases. I have
some code that deletes and then reinserts a row that has changes
instead of doing an UPDATE. As such, I re-use the same guid in the
ROWGUID column.
I have found that when I do this, the changes to the row do not make
it back up to the master server during a synchronize, as if the
replication does not recognize that the delete and re-insert has been
done on the row. If I do an update on the row, the change is
recognized and sent back to the master server.
Is this an expected behavior? Is it a no-no to delete a row with
ROWGUID x, then re-insert a new row with the same ROWGUID x?
I can probably whip up a step-by-step example that demonstrates this
behavior if desired.
thanks,
matthew tagliaferri
I take it you are using merge replication. Merge replication uses the
rowguid column to track which row has changed. Its probably not a good idea
to modify this row.
Can you check your conflict tables using the conflict viewer to see if any
conflicts are being logged. If not, please post your repo.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"matt tagliaferri" <mtagliaf@.cleindians.com> wrote in message
news:2cc74fa4.0411181102.5406ca87@.posting.google.c om...
> My ROWGUID columns are also my primary keys in many cases. I have
> some code that deletes and then reinserts a row that has changes
> instead of doing an UPDATE. As such, I re-use the same guid in the
> ROWGUID column.
> I have found that when I do this, the changes to the row do not make
> it back up to the master server during a synchronize, as if the
> replication does not recognize that the delete and re-insert has been
> done on the row. If I do an update on the row, the change is
> recognized and sent back to the master server.
> Is this an expected behavior? Is it a no-no to delete a row with
> ROWGUID x, then re-insert a new row with the same ROWGUID x?
> I can probably whip up a step-by-step example that demonstrates this
> behavior if desired.
> thanks,
> matthew tagliaferri
|||No conflicts exist. I can look at the table in question on the "master"
and "child" server, and the data in the child server is newer than the
data in the master server.
I spent a bit of time setting up some replication logging, this was
useful only in the fact that it didn't show any replication activity on
the table in question.
My collegue and I are setting up a reproducible example now, I will post
here when it is complete.
thanks for the reply,
matt tagliaferri
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!