Re: Clearer version of my earlier question re sp_grantlogin and sp_grantdbaccess

• Previous message: Clive Elsworth: "Re: Domain Admin rights needed to sp_grantlogin and sp_grantdbaccess?"
• Messages sorted by: [ date ] [ thread ]

Date: Sat, 3 Apr 2004 19:04:57 +0100

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...
> > 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...
> > > 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
> > > >
> > > >
> > >
> > >
> >
> >
> > ---
> > 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
> >
> >
>
>

---
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

• Previous message: Clive Elsworth: "Re: Domain Admin rights needed to sp_grantlogin and sp_grantdbaccess?"
• Messages sorted by: [ date ] [ thread ]