How do I prevent SQL Server sysadmin users write access ?

Question

I have created a test user that has sysadmin right in SQL Server 2005 (sysadmin, because i want to profile with this user name also).But i want to restrict that test users access rights to production database.

It is under "logins" and also db name selected under the "User Mapping" tab of its properties with "db_denydatareader" default schema. But it is still can run select statements.

Answer 1

Sorry but once you give SysAdmin access, you've given away the farm. You'll have to create a different role for the test user and then grant access only to the databases you want.