Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the SQL Server 2005 Express edition with "Mixed Mode authentication".

So I want to create a database called "ActivityLogs" where I want to DENY access for the "sa" login. I want to create another login called "AuditLogin" which should be able to access this database alone.

"ActivityLogs" should not be accessible to the "sa" login for dataaccess as well as schema access.

How can this be done?

share|improve this question

You can't deny anything to 'sa' (or, more correctly, to members of the sysadmin role). Inside the database, you can't deny anything to 'dbo' (or, more correctly, to members of db_owner role). That's just the way things are and there is nothing you can do to circumvent it.

If you want to audit a server you must upgrade to SQL 2008 and use the built-in audit infrastructure. This infrastructure is tamper-proof by design, so even though sysadmins can disable it, the disable is evident in the audit log.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.