Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

1 Answer 1

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

 
discard

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.