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?


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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