Re: SQL Server

From: BP Margolin (bpmargo@attglobal.net)
Date: 11/24/02 

From: "BP Margolin" <bpmargo@attglobal.net>
Date: Sun, 24 Nov 2002 15:41:04 -0500

Brett,

> 2. Server upgrades and other tasks require the user to log in as "sa",
> so the account cannot be removed. This was the part that I was
> wondering about, and if it were true.

Not an entirely accurate statement.

It is true that in order to apply, for example, a service pack to SQL Server
one has to (a) login as a member of the sysadmin role, and (b) have (almost)
administrator privileges on the machine. But one can be a member of the
sysadmin role without being "sa". The "sa" login is useful only when SQL
Server is in Mixed Authentication mode, so one can have SQL Server in
Windows Authentication mode, in which case the "sa" login just doesn't work
at all.

Additionally, even if the application requirements are such that one must
support Mixed Authentication mode, one can assign an "impossible" password
to the sa login (thereby making it effectively both hack proof and useless
for every day use as well), but still have individuals in the sysadmin role.
BTW, passwords can be up to 128 characters long :-)

You might consider taking a look at the documentation in the SQL Server
Books Online about the sysadmin fixed server role. Perhaps a good starting
place is the section "System Administrator (sa) Login".

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Brett Karst" <karst.brett@mayo.edu> wrote in message
news:eW19rV$kCHA.2616@tkmsftngp09...
> Thanks for the articles. I agree with you that the situation under
> which the sa account was being used was inappropriate. When I asked the
> administrators to create separate accounts, they argued that:
>
> 1. The standard SQL Server audit logs only indicate when a user logged
> on/off; not what they did. Utilizing the enhanced SQL Server auditing
> ("Profile"?) may require too many system resources, even if they were to
> just log the details of the individual sa accounts because the logging
> mechanism would have to verify whether each transaction was performed by
> an sa-privileged account.
>
> 2. Server upgrades and other tasks require the user to log in as "sa",
> so the account cannot be removed. This was the part that I was
> wondering about, and if it were true. They are somewhat open to the
> envelope method mentioned in the references you cited.
>
> Thanks again for your help.
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: MSSQL$SBSMONITORING Login to Disabled Account?
    ... SQL Server installed, I can't have the tools you were using, can I? ... I still don't know what that process is doing trying to login to the ... Administrator account and I don't understand why this login attempt is ...
    (microsoft.public.windows.server.sbs)
  • Re: xp_cmdshell issue, local system
    ... So initially I tried to change the login using EM, ... account to the localsystem for SQL Server and the same for agent which worked ... Now when I go and try the same for the agent startup account it ...
    (microsoft.public.sqlserver.security)
  • Re: Problems changing the password for the service account in SQL
    ... It is because there is BULTIN\Administrstors Login that alllow access to ... You have a domaim account group that SQL Server ... this domain account in SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: Builtin Administrators Group and SQL Agent Jobs
    ... >gave that login full access to all SQL DB's. ... >group and rights to all the SQL Server databases. ... >Administrator Server role explicitly. ... >account that is a member of the DBA group and this group ...
    (microsoft.public.sqlserver.security)
  • Re: The SA Mess
    ... I agree - no one needs to use the sa account. ... How does a firewall know that there was an unsuccessful login to SQL ... That info has to come from SQL Server. ...
    (microsoft.public.sqlserver.security)