New SQL Server Administration Forum 1

The purpose of this forum is to focus on SQL Server administration issues such as setup, SQL Mail, Backup, Restore, database maintenance, security, etc. Let's try keep the focus on related issues. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

I want to set up a restricted access instance of a SQL Server 2000 db on a common server. If I had my own server that I could dedciate to this one relatively small db I'd set it up locally and be done with it. Yet, if I need to set it up on a common server, by don't have the luxury to set myself as the sa on this server (for mssql purposes); what can be done to grant access to limited user sets while restricting the sa (who administers other db's on the same server) from accessing this restricted instance of data.

It is best to post new questions in nwe threads.

The answer to your question depends on the version and edition of SQL Server that you run. Why must the SA be restricted from the database? If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

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. i.e. db_denydatareader or db_denydatawriter cannot be applied to a sysadmin role.

datamat,
Well done for following Terry's advice and posting a new question. But now you've done that, there's no need to keep both posts going! Let's keep it all in one thread:

thread962-505719 --James