SQL2000 SA Account

SQL2000 SA Account

Post by Graham Mitchel » Fri, 16 May 2003 23:46:24



We've set up each of our programmers with their own Active Directory
SQLAdmin account, made them members of the SQL Server Admin group in AD. The
AD group has been added to the SQL server login IDs, and been given the
system administrator role.

What I'd like to do now is to basically turn the SA account into a 'user'
account, with as few rights as possible (actually, I'd like to delete it,
but you can't do that)... What's the best way to go about this...? Any
suggestions and pointers are gratefully accepted.

Graham

 
 
 

SQL2000 SA Account

Post by Jasper Smit » Sat, 17 May 2003 02:01:54


Do you use SQL authentication for anything, if not then
you can change the server to Windows only. However,
regardless of this make sure the sa login has a very strong
password and lock it away. The trouble is that anyone
who is a sysadmin can change the sa password without
knowing what it currently is so it won't do a hell of a lot of
good. As you have discovered, you can't remove the sa login
or remove it from the sysadmin role.

--
HTH

Jasper Smith (SQL Server MVP)

I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org


Quote:> We've set up each of our programmers with their own Active Directory
> SQLAdmin account, made them members of the SQL Server Admin group in AD.
The
> AD group has been added to the SQL server login IDs, and been given the
> system administrator role.

> What I'd like to do now is to basically turn the SA account into a 'user'
> account, with as few rights as possible (actually, I'd like to delete it,
> but you can't do that)... What's the best way to go about this...? Any
> suggestions and pointers are gratefully accepted.

> Graham


 
 
 

SQL2000 SA Account

Post by Graham Mitchel » Sat, 17 May 2003 03:16:18


Unfortunately we have to leave it in mixed mode, as the application we're
going to install only works with SQL Authentication.... sigh..... Another
reason for trying to get rid of the SA account, is that some programmers
think that the solution for any permission problems is to use SA.. so I'd
like to get rid of it to remove the temptation. I think I'll just change the
password and 'forget it'....:)

Graham


> Do you use SQL authentication for anything, if not then
> you can change the server to Windows only. However,
> regardless of this make sure the sa login has a very strong
> password and lock it away. The trouble is that anyone
> who is a sysadmin can change the sa password without
> knowing what it currently is so it won't do a hell of a lot of
> good. As you have discovered, you can't remove the sa login
> or remove it from the sysadmin role.

> --
> HTH

> Jasper Smith (SQL Server MVP)

> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org



> > We've set up each of our programmers with their own Active Directory
> > SQLAdmin account, made them members of the SQL Server Admin group in AD.
> The
> > AD group has been added to the SQL server login IDs, and been given the
> > system administrator role.

> > What I'd like to do now is to basically turn the SA account into a
'user'
> > account, with as few rights as possible (actually, I'd like to delete
it,
> > but you can't do that)... What's the best way to go about this...? Any
> > suggestions and pointers are gratefully accepted.

> > Graham

 
 
 

1. Installing SQL2000 SP1 kills sa and builtin\administrator accounts

Help I need to get the SQL agent working again for backups

after installing SQL SP1 I can not start SQL Agent no errors reported

changing sql agent login properties gives error below

Error 22002: RegCreateKeyEx() returend error 5, 'Access is denied'

clicking  sa or builtin/administrator user gives error below

Error 21776 [SQL-DMO]The name 'dbo' was not found in the user collection. If
the name is a qualified name use [] to seperate various parts of the name,
and try again

any Ideas would be helpful I can not restore master db error about created
in diffrent version

Is it safe to uninstall SP1 atlest the DB is working don't want to have to
do a complete rebuild. can do manual back ups.

Thanks

2. database not found ¨?

3. SA account and SA role

4. subscribe

5. sp_addumpdevice with non-sa account?

6. Moveprevious, Movenext

7. How to give sa-privileges to an account?

8. image problem

9. SA Account (SQL6.5)

10. Authentication Error using SA account

11. SQL blocks the ‘sa’ account

12. Tables become read only to sa account

13. Help Login fail using sa account