I am using the SQL Server Personal edition in remote offices with 2 or 3
clients each to connect to our central office server running SQL Server
Standard Edition. I want to use the personal edition because when I am
using replication between each client and main office and the EM will be
useful.
I am having connection problems when I use the personal edition but no
connection problems with the same setup using a developer edition. I
have read in places there are conection issues with the personal edition
but have not found many solutions or even details on the issues. It is
my understanding that the personal edition should work for this setup
and should communicate with Central servers and provide replication
capabilities.
My snapshot works fine but any transactions or Merge replication will
not replicate,I get a can not connect error. Again, the transactions
replicate when I use the developer edition on my client just not the
personal edition.
Can the personal edition communicate with standard edition? How does the
personal edition setup differ in order to work? Where can I find more
information on how to correct the problems (if any)? Are there any other
options (if this is not possible) without buying a stnd edition for each
office?
thanks,
eric.
Eric,
Personal Edition includes a concurrent workload governor that limits its
scalability; performance degrades when more than five Transact-SQL batches
are executed concurrently, which may be the cause of your issues. This is
really to prevent people using it for production systems, and upgrading to
workgroup or standard would solve your issues if this is the case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label central. Show all posts
Showing posts with label central. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
permissions problem
I have what I'm sure is a simple problem with setting permissions on some
objects. Here's the situation... I have one database (Central) that I want
to allow inserts to a table (Queue) on via a stored procedure. That stored
procedure I am storing in the master database so that any of the other
databases I have running can execute it.
I am doing the insert in the procedure by referencing "INSERT INTO
Central.dbo.Queue..." That procedure is set to allow EXEC permissions by
the guest and public roles.
I have logins from the other databases 'Permit in Database Role' set to
public for the Queue table. The error I am getting is "INSERT permission
denied on object 'Queue', database 'Central', owner 'dbo'.
What am I missing'> What am I missing'
If your stored procedure is owned by 'dbo', then your Central database needs
to also be owned by 'sa' so that the ownership chain is unbroken. Also, if
you are running SQL 2000 SP3+, you'll need to enable cross-database chaining
('db chaining' database option) un the Central database.
Note that you should enable cross-database chaining in an sa-owned database
when only sy
min role members can create dbo-owned objects in that
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Random" <cipherlad@.hotmail.com> wrote in message
news:u2otNyjOFHA.244@.TK2MSFTNGP12.phx.gbl...
>I have what I'm sure is a simple problem with setting permissions on some
>objects. Here's the situation... I have one database (Central) that I want
>to allow inserts to a table (Queue) on via a stored procedure. That stored
>procedure I am storing in the master database so that any of the other
>databases I have running can execute it.
> I am doing the insert in the procedure by referencing "INSERT INTO
> Central.dbo.Queue..." That procedure is set to allow EXEC permissions by
> the guest and public roles.
> I have logins from the other databases 'Permit in Database Role' set to
> public for the Queue table. The error I am getting is "INSERT permission
> denied on object 'Queue', database 'Central', owner 'dbo'.
> What am I missing'
>|||Thank you for replying. The owner on the Central database is 'sa'.
Where is the option for 'db chaining' set? I can't locate it in books
online.
I have also ensured that only sy
min members can create dbo-owned objects
in the database.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ecJw5QkOFHA.2708@.tk2msftngp13.phx.gbl...
> If your stored procedure is owned by 'dbo', then your Central database
> needs to also be owned by 'sa' so that the ownership chain is unbroken.
> Also, if you are running SQL 2000 SP3+, you'll need to enable
> cross-database chaining ('db chaining' database option) un the Central
> database.
> Note that you should enable cross-database chaining in an sa-owned
> database when only sy
min role members can create dbo-owned objects in
> that database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:u2otNyjOFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||The 'db chaining' database option is aka cross-database chaining. One
method to turn it on is with sp_dboption via Query Analyzer:
EXEC sp_dboption 'Central' ,'db chaining', true
Hope this helps.
Dan Guzman
SQL Server MVP
"Random" <cipherlad@.hotmail.com> wrote in message
news:OErPV0rOFHA.3444@.tk2msftngp13.phx.gbl...
> Thank you for replying. The owner on the Central database is 'sa'.
> Where is the option for 'db chaining' set? I can't locate it in books
> online.
> I have also ensured that only sy
min members can create dbo-owned
> objects in the database.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:ecJw5QkOFHA.2708@.tk2msftngp13.phx.gbl...
>|||FYI, setting the 'db_chaining' option did the trick. I found the
documentation and read about the security implications, so I've got it
covered now. Thanks for the tips!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OY7$75vOFHA.2520@.tk2msftngp13.phx.gbl...
> The 'db chaining' database option is aka cross-database chaining. One
> method to turn it on is with sp_dboption via Query Analyzer:
> EXEC sp_dboption 'Central' ,'db chaining', true
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:OErPV0rOFHA.3444@.tk2msftngp13.phx.gbl...
>
objects. Here's the situation... I have one database (Central) that I want
to allow inserts to a table (Queue) on via a stored procedure. That stored
procedure I am storing in the master database so that any of the other
databases I have running can execute it.
I am doing the insert in the procedure by referencing "INSERT INTO
Central.dbo.Queue..." That procedure is set to allow EXEC permissions by
the guest and public roles.
I have logins from the other databases 'Permit in Database Role' set to
public for the Queue table. The error I am getting is "INSERT permission
denied on object 'Queue', database 'Central', owner 'dbo'.
What am I missing'> What am I missing'
If your stored procedure is owned by 'dbo', then your Central database needs
to also be owned by 'sa' so that the ownership chain is unbroken. Also, if
you are running SQL 2000 SP3+, you'll need to enable cross-database chaining
('db chaining' database option) un the Central database.
Note that you should enable cross-database chaining in an sa-owned database
when only sy
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Random" <cipherlad@.hotmail.com> wrote in message
news:u2otNyjOFHA.244@.TK2MSFTNGP12.phx.gbl...
>I have what I'm sure is a simple problem with setting permissions on some
>objects. Here's the situation... I have one database (Central) that I want
>to allow inserts to a table (Queue) on via a stored procedure. That stored
>procedure I am storing in the master database so that any of the other
>databases I have running can execute it.
> I am doing the insert in the procedure by referencing "INSERT INTO
> Central.dbo.Queue..." That procedure is set to allow EXEC permissions by
> the guest and public roles.
> I have logins from the other databases 'Permit in Database Role' set to
> public for the Queue table. The error I am getting is "INSERT permission
> denied on object 'Queue', database 'Central', owner 'dbo'.
> What am I missing'
>|||Thank you for replying. The owner on the Central database is 'sa'.
Where is the option for 'db chaining' set? I can't locate it in books
online.
I have also ensured that only sy
in the database.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ecJw5QkOFHA.2708@.tk2msftngp13.phx.gbl...
> If your stored procedure is owned by 'dbo', then your Central database
> needs to also be owned by 'sa' so that the ownership chain is unbroken.
> Also, if you are running SQL 2000 SP3+, you'll need to enable
> cross-database chaining ('db chaining' database option) un the Central
> database.
> Note that you should enable cross-database chaining in an sa-owned
> database when only sy
> that database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:u2otNyjOFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||The 'db chaining' database option is aka cross-database chaining. One
method to turn it on is with sp_dboption via Query Analyzer:
EXEC sp_dboption 'Central' ,'db chaining', true
Hope this helps.
Dan Guzman
SQL Server MVP
"Random" <cipherlad@.hotmail.com> wrote in message
news:OErPV0rOFHA.3444@.tk2msftngp13.phx.gbl...
> Thank you for replying. The owner on the Central database is 'sa'.
> Where is the option for 'db chaining' set? I can't locate it in books
> online.
> I have also ensured that only sy
> objects in the database.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:ecJw5QkOFHA.2708@.tk2msftngp13.phx.gbl...
>|||FYI, setting the 'db_chaining' option did the trick. I found the
documentation and read about the security implications, so I've got it
covered now. Thanks for the tips!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OY7$75vOFHA.2520@.tk2msftngp13.phx.gbl...
> The 'db chaining' database option is aka cross-database chaining. One
> method to turn it on is with sp_dboption via Query Analyzer:
> EXEC sp_dboption 'Central' ,'db chaining', true
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:OErPV0rOFHA.3444@.tk2msftngp13.phx.gbl...
>
Labels:
central,
database,
microsoft,
mysql,
oracle,
permissions,
server,
setting,
situation,
someobjects,
sql
Subscribe to:
Posts (Atom)