Cluster questions

First of all, excuse me if my English isn't too correct.
I need to install a portal infrastructure that was developed with ASP
and SQL Server. The system would like to be redundant and scalable.
To do that, I have in mind, the following diagram :
- 2 W2003 Servers with IIS6 and NLB ( frontend )
- 2 W2003 Servers with MS-SQL and MSCS with a SAN connected ( backend
).
I have read Microsoft and others documentation, and I have questions
that I would like to know.
I have worked with NLB and I understand how it works, but I'm new with
MSCS, and I must need to know to cluster SQL servers.
In SQL 2000, the cluster can be active/pasive or active/active.
If it is active/pasive, one of the two nodes, assumes all work, and
the second node is waiting that the first node goes down, when that
happens the second node assumes all work, this is the failover. Every
node has got one IP and the two nodes have a IP that they share (
Virtual Server), like NLB.
The ASP applications connect to the IP of Virtual Server. No problem.
The cluster is transparent to the ASP aplication.
But if I have active/active mode, the application must see two SQL
Server, not one like active/pasive mode, it's true ? The cluster isn't
transparent to the ASP aplication ?
The developement of the ASP application is different that the
active/pasive and active/active modes ?
With MSCS is possible to make the system scalable or only the failover
is possible ? If it's possible the scalability, is necessary to modify
the ASP developement or it's transparent ?
I read that MSCS is necesary that the servers must be joined in a
domain, it can't work in a Workgroup.
If I install AD in the 2 web servers, the performance will decrease,
there won't be users and permissions, it's only a web portal.
Is it possible to work in a Workgroup ?
And at the end, give me your opinion about :
- Install SQL 2000 SP4 or SQL 2005 ?
- Install MSCS or a third party software, like Computer associates ?
Thanks in advance,
Xavi.
You seem to have a misunderstanding of what MSCS does and how it works with
SQL. That is not unexpected since the terminoloty is very confusing, even
to clustering veterans.
Here is an excellent starting point for understanding SQL Clustering.
http://www.microsoft.com/technet/pro.../failclus.mspx
The document describes SQL 2000 clustering but it applies to SQL 2005 as
well.
As for your questions, Active-Active and Active-Passive are no longer valid
terms to describe SQL Clusters. You now have single- or multi- instance
clusters. It sounds like you are looking for a single instance cluster with
two nodes. Aclustered instance looks just like any other SQL instance from
the client. There is no difference from an application perspective whether
a SQL instnace is clustered or not. An application should handle brief
database connection disruptions gracefully in case there is a cluster
failover, but that is part of good application design anyway.
If I was starting from scratch, I would use SQL 2005.
MSCS does require a domain and will not work in a Workgroup. Anytime I
build a highly available application system, I prefer to build a monitoring
and management console as part of the system. That can be your primary AD
controller. I also use it to manage backups and system monitoring/alerting
tools.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"xavi" <xavi@.comb.es> wrote in message
news:1130854797.337258.155860@.g49g2000cwa.googlegr oups.com...
> First of all, excuse me if my English isn't too correct.
> I need to install a portal infrastructure that was developed with ASP
> and SQL Server. The system would like to be redundant and scalable.
> To do that, I have in mind, the following diagram :
> - 2 W2003 Servers with IIS6 and NLB ( frontend )
> - 2 W2003 Servers with MS-SQL and MSCS with a SAN connected ( backend
> ).
> I have read Microsoft and others documentation, and I have questions
> that I would like to know.
> I have worked with NLB and I understand how it works, but I'm new with
> MSCS, and I must need to know to cluster SQL servers.
> In SQL 2000, the cluster can be active/pasive or active/active.
> If it is active/pasive, one of the two nodes, assumes all work, and
> the second node is waiting that the first node goes down, when that
> happens the second node assumes all work, this is the failover. Every
> node has got one IP and the two nodes have a IP that they share (
> Virtual Server), like NLB.
> The ASP applications connect to the IP of Virtual Server. No problem.
> The cluster is transparent to the ASP aplication.
> But if I have active/active mode, the application must see two SQL
> Server, not one like active/pasive mode, it's true ? The cluster isn't
> transparent to the ASP aplication ?
> The developement of the ASP application is different that the
> active/pasive and active/active modes ?
> With MSCS is possible to make the system scalable or only the failover
> is possible ? If it's possible the scalability, is necessary to modify
> the ASP developement or it's transparent ?
> I read that MSCS is necesary that the servers must be joined in a
> domain, it can't work in a Workgroup.
> If I install AD in the 2 web servers, the performance will decrease,
> there won't be users and permissions, it's only a web portal.
> Is it possible to work in a Workgroup ?
> And at the end, give me your opinion about :
> - Install SQL 2000 SP4 or SQL 2005 ?
> - Install MSCS or a third party software, like Computer associates ?
> Thanks in advance,
> Xavi.
>
|||As you can see, I'm not a cluster veteran :-)
Thanks for the info, and the link documentation is very good.
I understand the single-instance failover cluster, but I don't
understand too much multi-instance cluster.
Is there no difference from the application prespective in a
multi-instance cluster ?
It is possible to do in the scenario descrived ?
Is there any example of a multi-instance cluster ?
Thanks,
Xavi.
|||Each extra instance looks like another SQL Server as far as the clients are
concerned. Each clustered instance has independent disk resources, IP
address(es), and unique network name. You connect to a Virtual SQL Server,
not to the underlying host node.
You need to differentiate between Nodes (host computers) and instances
(Virtual SQL Servers) in a cluster. They exist independently.in a cluster.
You can have up to 16 instances and eight nodes in a cluster (SQL 2005).
You get four nodes under SQL 2000. If you need multiple clustered SQL
Servers, you can create multiple instances on a cluster and save some money.
If you need only one SQL Server, then you create a single-instance cluster.
SQL Clustering is a failover technology, not a scale-up technology. You
cannot have two host nodes connected to the same database for scale out.
That is why the Active-Active terminology is misleading.
Your general idea looks good.
GNH
"xavi" <xavi@.comb.es> wrote in message
news:1130878710.578655.77290@.f14g2000cwb.googlegro ups.com...
> As you can see, I'm not a cluster veteran :-)
> Thanks for the info, and the link documentation is very good.
> I understand the single-instance failover cluster, but I don't
> understand too much multi-instance cluster.
> Is there no difference from the application prespective in a
> multi-instance cluster ?
> It is possible to do in the scenario descrived ?
> Is there any example of a multi-instance cluster ?
> Thanks,
> Xavi.
>
|||You can run SQL Server in NLB, which is known as a Federation of Servers.
Server clusters are for redundancy and high availability, not scalability.
If you are looking for scalability, which does provide some high
availability, you should look at server federations and distributed
partitioned views. This is most often used for partitioned VLB
installations, mostly warehouse, read-only type systems, rarely OLTP
systems.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OuR$Tw13FHA.3600@.TK2MSFTNGP12.phx.gbl...
> Each extra instance looks like another SQL Server as far as the clients
are
> concerned. Each clustered instance has independent disk resources, IP
> address(es), and unique network name. You connect to a Virtual SQL
Server,
> not to the underlying host node.
> You need to differentiate between Nodes (host computers) and instances
> (Virtual SQL Servers) in a cluster. They exist independently.in a
cluster.
> You can have up to 16 instances and eight nodes in a cluster (SQL 2005).
> You get four nodes under SQL 2000. If you need multiple clustered SQL
> Servers, you can create multiple instances on a cluster and save some
money.
> If you need only one SQL Server, then you create a single-instance
cluster.
> SQL Clustering is a failover technology, not a scale-up technology. You
> cannot have two host nodes connected to the same database for scale out.
> That is why the Active-Active terminology is misleading.
> Your general idea looks good.
> GNH
> "xavi" <xavi@.comb.es> wrote in message
> news:1130878710.578655.77290@.f14g2000cwb.googlegro ups.com...
>
|||I see that, in the main schema, the SQL servers that use MSCS must be
joined in a domain.
The Geoff N. Hiten suggestion about using an other server as a DC seems
a good idea, but what does it happens if this DC server fails? Does the
SQL cluster continue alive ?
What are the consecuences of a DC failure ?
I would not like using 2 aditional servers for 2 DCs just for DC
redundance.
Thanks,
Xavi.
|||Hi
You loose your DC, your cluster goes down.
If you need the resilience of a cluster, and can afford all that expensive
hardware, having small servers as 2 DC's is not much to ask for.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"xavi" <xavi@.comb.es> wrote in message
news:1133345191.463210.32350@.g47g2000cwa.googlegro ups.com...
>I see that, in the main schema, the SQL servers that use MSCS must be
> joined in a domain.
> The Geoff N. Hiten suggestion about using an other server as a DC seems
> a good idea, but what does it happens if this DC server fails? Does the
> SQL cluster continue alive ?
> What are the consecuences of a DC failure ?
> I would not like using 2 aditional servers for 2 DCs just for DC
> redundance.
> Thanks,
> Xavi.
>

Client Tools for Backup

Hi all,
I hope this hasn't been answered already...
I have developed a .NET WinForms app which runs on client PCs which talk to
SQL Server2000 on a server. I have coded a backup screen which uses
SQLDMO. This runs fine when I run the app on the server, but it crashes
when I try it on the client PCs. Looks like something to do with an
unregistered
SQLDMO dll.
There's only one client PC which actually requires this functionality. I'm
thinking that installing Client Tools on this PC should take care of things.
I'm wondering if there are any licensing issues.
The company in question is running Small Business Server 2000 and has
the relevant licenses for SQL Server 2000.
Thanks!
John
See if this helps:
http://support.microsoft.com/default...b;EN-US;248241
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"jonsie" <jonsie@.discussions.microsoft.com> wrote in message
news:0F7A3DB9-C07E-4595-9D1A-B19300024332@.microsoft.com...
Hi all,
I hope this hasn't been answered already...
I have developed a .NET WinForms app which runs on client PCs which talk to
SQL Server2000 on a server. I have coded a backup screen which uses
SQLDMO. This runs fine when I run the app on the server, but it crashes
when I try it on the client PCs. Looks like something to do with an
unregistered
SQLDMO dll.
There's only one client PC which actually requires this functionality. I'm
thinking that installing Client Tools on this PC should take care of things.
I'm wondering if there are any licensing issues.
The company in question is running Small Business Server 2000 and has
the relevant licenses for SQL Server 2000.
Thanks!
John
|||hi John,
"jonsie" <jonsie@.discussions.microsoft.com> ha scritto nel messaggio
news:0F7A3DB9-C07E-4595-9D1A-B19300024332@.microsoft.com...
> Hi all,
> I hope this hasn't been answered already...
> I have developed a .NET WinForms app which runs on client PCs which talk
to
> SQL Server2000 on a server. I have coded a backup screen which uses
> SQLDMO. This runs fine when I run the app on the server, but it crashes
> when I try it on the client PCs. Looks like something to do with an
> unregistered
> SQLDMO dll.
> There's only one client PC which actually requires this functionality. I'm
> thinking that installing Client Tools on this PC should take care of
things.
> I'm wondering if there are any licensing issues.
> The company in question is running Small Business Server 2000 and has
> the relevant licenses for SQL Server 2000.
the SQL Server Client Tools are limited and related to SQL Server license...
regarding SQL-DMO component, you can distribute it as it is freely
redistributable..
the related dependencies are:
; not licensed by redist.txt but available after installation of MDAC2.6
...\WINDOWS\SYSTEM\odbcbcp.dll; DestDir: WinSys ; sharedfile
; not licensed by redist.txt but available after installation of MDAC2.6
...\WINDOWS\SYSTEM\sqlwoa.dll ; DestDir: WinSys
; not licensed by redist.txt but available after installation of MDAC2.6
...\WINDOWS\SYSTEM\sqlwid.dll ; DestDir: WinSys
...\Programmi\Microsoft SQL Server\80\Tools\Binn\w95scm.dll; DestDir:
DestinationFolder\Binn
...\WINDOWS\SYSTEM\sqlunirl.dll ; DestDir: WinSys
...\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlresld.dll; DestDir:
DestinationFolder\Binn
...\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll; DestDir:
DestinationFolder\Binn
; not licensed by redist.txt but available after installation of MDAC2.6
...\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\sqlsvc.RLL;
DestDir: DestinationFolder\Binn\Resources\1033
; not licensed by redist.txt but available after installation of MDAC2.6
...\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\Sqldmo.rll;
DestDir: DestinationFolder\Binn\Resources\1033
...\Programmi\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll; DestDir:
DestinationFolder\Binn ; file to be registered via regserver
DestinationFolder can either be the installation directory of one instance
of Microsoft SqlServer 2000, like ..\Program Files\Microsoft SQL
Server\80\Tools, even if no istance of SQL Server has been installed, or the
installation directory of your application, but the first one is preferred.
Please do respect the hierarchy \Binn\Resources\1033 (where 1033 specifies
the language), where needed, in order to grant correct functionality of
Ole-Automation objects.
In order to install SQL-DMO components for MSDE 2000, Microsoft Internet
Explorer 5.5 or higher is required.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Clicking print from Reporting Services report cause computer to re

I have a report developed using Reporting Services 2005 with SQL Server 2005.
It runs just fine, and printed fine up until a few days ago. Now, after I
run the report and it displays on the screen, clicking print causes the
computer to reboot. Not all the reports do this, just this one. I don't
know of anything that has changed that could cause this problem. Anyone have
a similar experience? The report uses a stored procedure to build a table.
But, again it did work o.k. until a few days ago.
ThanksSAME PROBLEM HERE !!
Our client called us yesterday, everything worked fine and now clicking the
print button on 1 or 2 reports just REBOOT the computer !
We are using Reporting Services 2000 with SQL Server 2000.
Please help!
"Glenna" wrote:
> I have a report developed using Reporting Services 2005 with SQL Server 2005.
> It runs just fine, and printed fine up until a few days ago. Now, after I
> run the report and it displays on the screen, clicking print causes the
> computer to reboot. Not all the reports do this, just this one. I don't
> know of anything that has changed that could cause this problem. Anyone have
> a similar experience? The report uses a stored procedure to build a table.
> But, again it did work o.k. until a few days ago.
> Thanks|||We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
patches?
"KhAoS" wrote:
> SAME PROBLEM HERE !!
> Our client called us yesterday, everything worked fine and now clicking the
> print button on 1 or 2 reports just REBOOT the computer !
> We are using Reporting Services 2000 with SQL Server 2000.
> Please help!
> "Glenna" wrote:
> > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > It runs just fine, and printed fine up until a few days ago. Now, after I
> > run the report and it displays on the screen, clicking print causes the
> > computer to reboot. Not all the reports do this, just this one. I don't
> > know of anything that has changed that could cause this problem. Anyone have
> > a similar experience? The report uses a stored procedure to build a table.
> > But, again it did work o.k. until a few days ago.
> >
> > Thanks|||On Apr 18, 10:36 am, pbriggsiberia
<pbriggsibe...@.discussions.microsoft.com> wrote:
> We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
> patches?
>
> "KhAoS" wrote:
> > SAME PROBLEM HERE !!
> > Our client called us yesterday, everything worked fine and now clicking the
> >printbutton on 1 or 2 reports justREBOOTthe computer !
> > We are using Reporting Services 2000 with SQL Server 2000.
> > Please help!
> > "Glenna" wrote:
> > > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > > It runs just fine, and printed fine up until a few days ago. Now, after I
> > > run the report and it displays on the screen, clickingprintcausesthe
> > > computer toreboot. Not all the reports do this, just this one. I don't
> > > know of anything that has changed that could cause this problem. Anyone have
> > > a similar experience? The report uses a stored procedure to build a table.
> > > But, again it did work o.k. until a few days ago.
> > > Thanks- Hide quoted text -
> - Show quoted text -
I am having the same problem. Has anyone found the solution to this?|||Found the solution on another forum!!
The problem come from a recent update patch from microsoft that affects some
printers drivers.
To get rid of the problem, install this hotfix (KB935843) :
http://www.microsoft.com/downloads/details.aspx?familyid=69443B00-F831-4785-B292-DBF8CF002C6A&displaylang=en
More infos : http://support.microsoft.com/?kbid=935843
"jrichardson2@.chkenergy.com" wrote:
> On Apr 18, 10:36 am, pbriggsiberia
> <pbriggsibe...@.discussions.microsoft.com> wrote:
> > We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
> > patches?
> >
> >
> >
> > "KhAoS" wrote:
> > > SAME PROBLEM HERE !!
> > > Our client called us yesterday, everything worked fine and now clicking the
> > >printbutton on 1 or 2 reports justREBOOTthe computer !
> > > We are using Reporting Services 2000 with SQL Server 2000.
> >
> > > Please help!
> >
> > > "Glenna" wrote:
> >
> > > > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > > > It runs just fine, and printed fine up until a few days ago. Now, after I
> > > > run the report and it displays on the screen, clickingprintcausesthe
> > > > computer toreboot. Not all the reports do this, just this one. I don't
> > > > know of anything that has changed that could cause this problem. Anyone have
> > > > a similar experience? The report uses a stored procedure to build a table.
> > > > But, again it did work o.k. until a few days ago.
> >
> > > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> I am having the same problem. Has anyone found the solution to this?
>

Clearer version of my earlier question re sp_grantlogin and sp_grantdbaccess

I have amended this post from the one I posted a few hours ago - to make it
clearer (and friendlier!).
I have developed an app for my customer and it assigns different privilege
levels to users based on which domain network group they belong to.
However the only way I have found to be able to grant them access to the sql
server and
database is to connect with a trusted connection having logged in as Domain
Admin. Is that correct?
(I have to go to another site in order to be allowed to do this which is
somewhat inconvenient).
What I would like to able to do but cannot is log into MSDE as sa and run
the following:
use AppDB
exec sp_grantlogin 'TheirDomain\AppManagers'
exec sp_grantdbaccess 'TheirDomain\AppManagers'
exec sp_grantlogin 'TheirDomain\AppReadWrite'
exec sp_grantdbaccess 'TheirDomain\AppReadWrite'
etc.
Can someone let me know if I could achieve the above with a trusted
connection to MSDE as
something less than Domain Admin? If so what network rights would I need?
Many thanks in advance, for your time and expertise!
Clive Elsworth (London UK)
www.EndorphinSoftware.co.uk
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.631 / Virus Database: 404 - Release Date: 17/03/2004
First question is whether the SQL Server install is a default install or if you can add some login first.
If it is a default install, the only logins which exists are the Windows Administrators group and "sa" (which
requires mixed mode - not recommended). However, in order to install SQL Server, you need to be Administrator
anyhow, so why not just run the script which creates the users after install? Am I missing something?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in message
news:OrihgJpEEHA.3864@.TK2MSFTNGP12.phx.gbl...
> I have amended this post from the one I posted a few hours ago - to make it
> clearer (and friendlier!).
> I have developed an app for my customer and it assigns different privilege
> levels to users based on which domain network group they belong to.
> However the only way I have found to be able to grant them access to the sql
> server and
> database is to connect with a trusted connection having logged in as Domain
> Admin. Is that correct?
> (I have to go to another site in order to be allowed to do this which is
> somewhat inconvenient).
> What I would like to able to do but cannot is log into MSDE as sa and run
> the following:
> use AppDB
> exec sp_grantlogin 'TheirDomain\AppManagers'
> exec sp_grantdbaccess 'TheirDomain\AppManagers'
> exec sp_grantlogin 'TheirDomain\AppReadWrite'
> exec sp_grantdbaccess 'TheirDomain\AppReadWrite'
> etc.
> Can someone let me know if I could achieve the above with a trusted
> connection to MSDE as
> something less than Domain Admin? If so what network rights would I need?
> Many thanks in advance, for your time and expertise!
> Clive Elsworth (London UK)
>
> --
> www.EndorphinSoftware.co.uk
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.631 / Virus Database: 404 - Release Date: 17/03/2004
>
|||Tibor
Hello again - you and I were in contact about 4 years ago or so - I don't
think I've been to Sweden since then unfortunately. It may have been my SQL
Beautifier that we were discussing then, which after giving away a fair bit
I have not developed since. In any case you were a great help with a trick
to restart SQL Agent when SQL Server restarts (using sp_procoption) which I
have since given away to countless students in classes I have taught.
Anyhow..
I did the install about a month ago as Domain Admin (default install - mixed
mode - not recommended I know, but I really need it) and ran the
sp_grantlogin and sp_grantdbaccess fine, because I was then at the 'Admin'
site where they let me login as Domain Admin. Since then, they wanted a
minor enhancement which meant altering a number of Tables, SPs and Views.
What I usually like to do in this situation is:
1. - Backup the customer's DB and restore it to my laptop
2. - Transfer the customer's data to the newly enhanced DB on my laptop,
which I have tested with the app back in my office
3. - Transfer and Restore that DB to the customer server PC
4. - Grant rights to network groups to the DB now on the customer server PC
In the end I had to do it the other way which was to:
1. - Backup the customer's DB and restore it to my laptop
2. - Apply all the changes to their DB and hope I hadn't missed anything.
3. - Restore it back to their server - the domain groups still existed as DB
users and so no further action was necessary.
Maybe I'll just have to invest in one of those utilities that tells you all
the differences between two DBs - so I can be sure I never miss anything -
but I'd rather not if possible.
It doesn't seem right that sa - that should have total control over a SQL
Server, doesn't have the right to grant DB access to Domain Groups. What do
you think?
Best Regards
Clive
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exVYrQpEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> First question is whether the SQL Server install is a default install or
if you can add some login first.
> If it is a default install, the only logins which exists are the Windows
Administrators group and "sa" (which
> requires mixed mode - not recommended). However, in order to install SQL
Server, you need to be Administrator
> anyhow, so why not just run the script which creates the users after
install? Am I missing something?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in
message
> news:OrihgJpEEHA.3864@.TK2MSFTNGP12.phx.gbl...
it
privilege
sql
Domain
run
need?
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.631 / Virus Database: 404 - Release Date: 18/03/2004
|||> Hello again - you and I were in contact about 4 years ago or so <snip>
Ahh, yes. Thanks for reminding me, Clive. :-)

> Maybe I'll just have to invest in one of those utilities that tells you all
> the differences between two DBs - so I can be sure I never miss anything -
> but I'd rather not if possible.
I take it that you have already determined that it is too labor intensive to continuously add onto a script
file while you do changes, so the script file in the end contains the necessary ALTER TABLE commands etc? This
is perfectly doable, but only you can determine whether you consider this hinders your development too much to
be worth it.

> It doesn't seem right that sa - that should have total control over a SQL
> Server, doesn't have the right to grant DB access to Domain Groups. What do
> you think?
Seems I missed this in the beginning of the thread. I thought that your problem is that you don't are
connected as sysadmin. I realize now that you are connected as sa. I didn't know that an SQL Server login as
sa doesn't give you the ability to add Windows Logins from a domain as database users to a database, and if
you read the documentation for sp_grantdbaccess, it only say that you need to be sysadmin (etc). If this is
what you are seeing, then you might want to post a bug report (or rather open a case which might end up in a
bug report being filed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in message
news:%23Zd5q5vEEHA.1376@.TK2MSFTNGP10.phx.gbl...
> Tibor
> Hello again - you and I were in contact about 4 years ago or so - I don't
> think I've been to Sweden since then unfortunately. It may have been my SQL
> Beautifier that we were discussing then, which after giving away a fair bit
> I have not developed since. In any case you were a great help with a trick
> to restart SQL Agent when SQL Server restarts (using sp_procoption) which I
> have since given away to countless students in classes I have taught.
> Anyhow..
> I did the install about a month ago as Domain Admin (default install - mixed
> mode - not recommended I know, but I really need it) and ran the
> sp_grantlogin and sp_grantdbaccess fine, because I was then at the 'Admin'
> site where they let me login as Domain Admin. Since then, they wanted a
> minor enhancement which meant altering a number of Tables, SPs and Views.
> What I usually like to do in this situation is:
> 1. - Backup the customer's DB and restore it to my laptop
> 2. - Transfer the customer's data to the newly enhanced DB on my laptop,
> which I have tested with the app back in my office
> 3. - Transfer and Restore that DB to the customer server PC
> 4. - Grant rights to network groups to the DB now on the customer server PC
> In the end I had to do it the other way which was to:
> 1. - Backup the customer's DB and restore it to my laptop
> 2. - Apply all the changes to their DB and hope I hadn't missed anything.
> 3. - Restore it back to their server - the domain groups still existed as DB
> users and so no further action was necessary.
> Maybe I'll just have to invest in one of those utilities that tells you all
> the differences between two DBs - so I can be sure I never miss anything -
> but I'd rather not if possible.
> It doesn't seem right that sa - that should have total control over a SQL
> Server, doesn't have the right to grant DB access to Domain Groups. What do
> you think?
> Best Regards
> Clive
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:exVYrQpEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> if you can add some login first.
> Administrators group and "sa" (which
> Server, you need to be Administrator
> install? Am I missing something?
> message
> it
> privilege
> sql
> Domain
> run
> need?
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.631 / Virus Database: 404 - Release Date: 18/03/2004
>
|||Tibor
Please see my reply to Greg Low about the sp_grantlogin problem. It's
basically still unresolved - I have more tests to do.

> I take it that you have already determined that it is too labor intensive
to continuously add onto a script
> file while you do changes, so the script file in the end contains the
necessary ALTER TABLE commands etc? This
> is perfectly doable, but only you can determine whether you consider this
hinders your development too much to
> be worth it.
Up to now I have got away without needing to do that because I've been
transferring the client's data into my own 'upgraded and fully tested' db,
before restoring it to their computers.
I was shocked the other day to learn that that was no longer going to be
possible in all cases, although luckily in that case I had kept a note of
all the changes I had made - and so made them all again manually to their
version of the db (briefly restored onto my laptop), which had security all
set up, and so could be restored back to their network without the need for
any additional sp_grantlogins.
Thanks for your help. Hope business is good for you.
Regards
Clive
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eFRGgzZFEHA.2408@.TK2MSFTNGP10.phx.gbl...
> Ahh, yes. Thanks for reminding me, Clive. :-)
>
all
anything -
> I take it that you have already determined that it is too labor intensive
to continuously add onto a script
> file while you do changes, so the script file in the end contains the
necessary ALTER TABLE commands etc? This
> is perfectly doable, but only you can determine whether you consider this
hinders your development too much to
> be worth it.
>
SQL
What do
> Seems I missed this in the beginning of the thread. I thought that your
problem is that you don't are
> connected as sysadmin. I realize now that you are connected as sa. I
didn't know that an SQL Server login as
> sa doesn't give you the ability to add Windows Logins from a domain as
database users to a database, and if
> you read the documentation for sp_grantdbaccess, it only say that you need
to be sysadmin (etc). If this is
> what you are seeing, then you might want to post a bug report (or rather
open a case which might end up in a
> bug report being filed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in
message
> news:%23Zd5q5vEEHA.1376@.TK2MSFTNGP10.phx.gbl...
don't
SQL
bit
trick
which I
mixed
'Admin'
Views.
PC
anything.
as DB
all
anything -
SQL
What do
in
or
Windows
SQL
make
the
which is
and
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 31/03/2004