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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Clive Elsworth (clive_at_takethisbitout.elsworth.dircon.co.uk)
Date: 03/26/04 

Date: Fri, 26 Mar 2004 06:49:39 -0000

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

Relevant Pages

  • RE: How do I get back to base SQL server on my SBS 2003 server?
    ... What's the error message you see when installing SQL? ... the easier way is to restore the server from recent backup. ... Screen shot of the error when you install SQL 2005 Workgroup edition. ... <The server-based applications that were running fine are: McAFee EPO, ...
    (microsoft.public.windows.server.sbs)
  • Re: SBS 2003 and Sql Server ~ Client Install
    ... I've found the client tools and have now ... A word mail merge document could access a SQL database via ... We want to install to gain an understanding. ... Once we've managed to install the SQL Server Client we can start to ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: how do I change the user install wizard to create user folder in different location?
    ... Some people read the instructions in such a way that they install both a new ... instance of sql, ... Never slight the SBS wizards. ... I reinstalled from scratch using the Dell OpenManage Server ...
    (microsoft.public.windows.server.sbs)
  • Re: SQL account rights
    ... Please advice what is the best, suitable rights rather than domain admin ... issues, such as a server that might have IIS running on the same machine, ... applicable to SQL 2000 environment, ... files, or backups, make sure that the service account has Full ...
    (microsoft.public.sqlserver.security)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)