Hi, I am not sure if this is possible, but here goes.
Can a user who is a member of the sysadmin role have specific access rights revoked to restrict their access?
The problem I have is, we recently moved a number of databases from our 2005 instance to a new 2008 instance. One of the applications used a sql login which I configured and gave dbowner rights on its database and public rights on the server login. When the application was tested it kept failing with failed logins to SQL Server. After speaking with the application's support team they asked about the SQL Login and said the login should be a member of the sysadmin server role. I said I didn't want it to be as it is a security risk to our database server which is used by lots of other applications. I asked if they could tell me what specific rights the login needed, but they didn't know, only that it needed sysadmin rights. For the minute I have given it sysadmin rights and the application is working fine.
Obviously, I would like to restrict this user a bit, but it is difficult to know what rights it requires as the application company wont/cant tell me. Can you give a login sysadmin roles, but revoke access to the login for certain databases?
Any help would be great.