Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
sa be gone!
- Thread starter datamat
- Start date
I don't think you can. The follwoing is from the SQL 2000 books online :
"System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use sa only when there is no other way to log in to an instance of Microsoft® SQL Server™ (for example, when other system administrators are unavailable or have forgotten their passwords)."
"System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use sa only when there is no other way to log in to an instance of Microsoft® SQL Server™ (for example, when other system administrators are unavailable or have forgotten their passwords)."
SemperFiDownUnda
Instructor
If you want to make sure no one can hack in then set a good 128 character password. Trust me no one without proper access already will ever get in via the sa account...ok well maybe but you know trees may start walking around tommorrow too.
- Thread starter
- #4
Using SQL Server 2000. We want to restrict DML access to the data in a particular db from sa. Yet, sa is granted sysadmin privs across all db's on the server. Can these privs be restricted for sa or any other sysadmin on this one db or does the db have to reside on a separate server altogether to gain this type of restricted access?
The problem stems from the fact that once the sysadmin is established on the server, it doesn't appear that any controls can be placed on that sysadmin with respect to reading and writing data on just one of many db's that are administered on that server. i.e. db_denydatareader or db_denydatawriter cannot be applied to a sysadmin role.
The problem stems from the fact that once the sysadmin is established on the server, it doesn't appear that any controls can be placed on that sysadmin with respect to reading and writing data on just one of many db's that are administered on that server. i.e. db_denydatareader or db_denydatawriter cannot be applied to a sysadmin role.
tlbroadbent
MIS
Are you running Enterprise or Standard Edition of SQL Server 2000? You can run multiple instances of SQL Server with either edition but you must license each instance if running Standard edition.
You could run another instance of SQL Server and use a different SA passsword on that edition so the system admin or DBA would not have any access there. You would also want to remove the Built-In administrators Windows logins.
As SemperFiDownUnda has noted, you cannot prevent the SA login from making schema or data changes. SA is a DBO in every database on the server. The only thing that can be done is strongly password SA, and limit the number of users with SA privileges.
Only Database Administrators should have SA privileges. I don't understand why a DBA should be prevented from making changes to databases. That is part of the job description. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:
You could run another instance of SQL Server and use a different SA passsword on that edition so the system admin or DBA would not have any access there. You would also want to remove the Built-In administrators Windows logins.
As SemperFiDownUnda has noted, you cannot prevent the SA login from making schema or data changes. SA is a DBO in every database on the server. The only thing that can be done is strongly password SA, and limit the number of users with SA privileges.
Only Database Administrators should have SA privileges. I don't understand why a DBA should be prevented from making changes to databases. That is part of the job description. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:
It seems to me that if you have some particular data that you want restricted to only one sa (as opposed to others who need these rights on other databases), your best option is to bite the bullet and buy a separate server. I can see where this might be true for some production data, especially accounting or payroll data. Remember that at least one person (and preferably two people) need to have SA rights to the server no matter what.
If your problem is that currently too many people have sa rights, the solution is simple. Take them away. Developers in particular do not need sa rights and should not have them on production even if they do have them on development.
If the problem is that you have one sa you do not trust, the fire him or her.
If the problem is that some of the other applications used sa as their connection from the user interface, then change this immediately and then immediately change the sa password as all your data is seriously at risk.
If your problem is that currently too many people have sa rights, the solution is simple. Take them away. Developers in particular do not need sa rights and should not have them on production even if they do have them on development.
If the problem is that you have one sa you do not trust, the fire him or her.
If the problem is that some of the other applications used sa as their connection from the user interface, then change this immediately and then immediately change the sa password as all your data is seriously at risk.
- Thread starter
- #7
I appreciate all your feedback. SQLSister hit the nail on the head with respect to payroll production data. I was hoping there was an alternative that permited a db_owner to control access outside the sysadmin's span of control at the database level.
Thanks ALL!!
Thanks ALL!!
We have all our payroll records on a separate server and only one non-accounting person has access to it in any way shape or form. This really is the best solution. Our Accounting server is also set up by the network admin guy so that no requests can come to it from outside the Firewall while our other database servers allow web access.
SemperFiDownUnda
Instructor
One thing to remember is that though you might find some way to lock users out of a database on a server but if that person has access to the machine they can get to the actual database files and copy and attach them to another server and browse around all they want. So if you want to stop them from reading this is like putting up wall in your front yard to keep out the neighbors dogs but your driveway isn't blocked in any way.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question