| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2003-01-04 : 22:22:16
|
| Hello...Is it possible to lock the 'sa' password? I know that using the OSQL utility you can use the sp_password command to change a password, without even having to know the original value. Would removing the sp_password Stored Proc be the only way to accomplish this?thanks.... |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-04 : 22:45:02
|
From Books Online, sp_password:quote:
PermissionsExecute permissions default to the public role for a user changing the password for his or her own login. Only members of the sysadmin role can change the password for another user's login
As far as sa is concerned, only another sysadmin could change it. Therefore, make sa the only sysadmin member, change the sa password, and don't share it with anyone. Create a separate login for each user that needs specific permissions, DO NOT allow anyone else to log in under sa. |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2003-01-04 : 23:46:01
|
| Great advice and information. Fortunately, we only have 'sa' in our sysadmin group...but I didn't think about the other issues you raised.Thanks! |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-01-05 : 10:27:08
|
| Is there any reason you can't use NT Authentication. You shouldn't really ever use sa to connect to SQL Server for anything. If NT Authentication is available, add your NT login and add it to the sysadmin role, if there are numerous admins use a local or domain local (if AD is available) group. You should never use sa to connect to the server - it's just not secure and too well known. If you must use SQL Authentication for login then create a separate login for yourself and add it to the sysadmin role.Remember that passwords for SQL logins are sent across the wire using really weak encryption and are insecure. Also, apply all the security hotfixes ( I know they're a pain to install at the moment but that will get better soon) as they close some gaping security holes in SQL server. As an example, on SQL2000 SP2 using default options, ANY login can gain sysadmin access and local administrator rights to the server in about 20 seconds (or less) !HTHJasper Smith |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2003-01-06 : 15:04:26
|
| Hi...we can't use NT Auth...because database access will be across the internet, but not via VPN. These sql login users are not part of the sysadmin group and only have been granted permission to one database. We have 'sa' around just for ourselves....but I'll take your advice and make sure that any developers/admin user logins are part of the sysadmin group. Though, for ourselves, we do use NT Auth to access the server. We have applied the latest Service Packs and patches. Thanks for the advice and help....dhw |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-06 : 15:29:54
|
| I'll probably get slapped for this, but...You might want to reconsider putting developers and admins in the sysadmin role. On systems where development occurs, developers should have dbo access on the databases that they are developing on. Server admins will by default be part of the sysadmin role as long as they are part of the administrator group on Windows. Other admins should be given specific roles. Only DBAs should be members of the sysadmin role (also default groups will be members) and know the sa password.Production should be locked down even further.Where I work, we follow the above guidelines and then lax it a bit in the system test environment.These are only recommendations. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-06 : 15:51:53
|
| You won't get slapped, I agree with you 100%. I'd also stress your point about "developers should have dbo access ON THE DATABASES THAT THEY ARE DEVELOPING ON". Developers should NOT have dbo access on a production server; that should also rest solely with the DBA. If you have only one server and can't create another database for development purposes, you should be very careful about what rights to give developers. Maybe team leaders could have dbo access only, for example.I think that since you can access the server using NT auth for admin purposes, only NT logins should be part of sysadmin and serveradmin roles. That will further secure your server in case someone's SQL Server login gets compromised. By making all the sysadmins NT users, they'd have to authenticate to your Windows domain before they can do anything. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-01-06 : 16:25:11
|
Couldn't agree more with the 2 previous posts, there is NEVER any need for developers to be part of the sysadmin fixed server role on any server (DEV,QA,UAT,PROD). And that goes for server OS admins too.Bah humbug  HTHJasper Smith |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2003-01-06 : 17:03:01
|
quote:
(DEV,QA,UAT,PROD)
I get DEV, QA, and PROD but what is UAT?UnAdulterated Transactions ?Uneven Argyle Trousers ?Unreliable Aged Trojans (the condoms or the ancient Greeks, both work) ?Undulating Acrobatic Temptresses ?Undercooked Albacore Tail ?Justin |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-06 : 17:20:55
|
| User Acceptance Testing! Don't you know your Dilbert-speak Justin? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-06 : 17:39:10
|
| Over here, we also have unit test environment, which is abbreviated as UNT. When the developers are ready to deploy a new version, we first install it on UNT, then test it out (which they call sanity). If everything tests fine, we then deploy to the QA environment. There are usually numerous builds that occur before we go to PROD. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-01-06 : 17:50:39
|
| Just as well you don't have a Compliance Unit Test Environment.Or a Colocated Unit Test Environment.OK, I'll stop nowDamian |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2003-01-08 : 13:16:56
|
| Thanks for everyone's comments and advice. I can see that it does make sense to remove the devs from the sysadmin group and only give them access to the db's or servers that they are developing on/with.thanks again! |
|
|
|
|
Lock 'sa' password?