I am using sql server 7.0, I want to deny permission to certain extended procedures in the master database to all the dbowners of other databases. Please tell me the syntax of the command with example.
Make sure the dbo accounts don't have explicit access to your master database (the dbo in master is not necessarily the same as the dbo in the other databases). Then deny permission to the guest user role in master for whatever procedures you want to disallow.
Since you are making a change in your master database, be sure to back it up!
I want to deny the permission to dbowners for getting the directory list when they click on the backup destination for taking backups. I have denied execute pemission to guest and public for xp_availablemedia but still the dbowners are able to get the directory list. Can you please suggest how to achieve this?
"Only members of the SQL Server system administrators role (sysadmin) can view the directory structure and select local backup files. "
Try going into the SQL Server Security/Logins folder in Enterprise Manager. Right click on the login, select Properties, and go to the Server Roles tab to make sure that the login is not a member of the system administrators server role.
"Try going into the SQL Server Security/Logins folder in Enterprise Manager. Right click on the login, select Properties, and go to the Server Roles tab to make sure that the login is not a member of the system administrators server role."
I checked it and the login does not have any role selected. The login has been made dbowner of one database only and cannot access any other database. Still the login is able to browse the directory tree. Any other seggestions.
What about looking at the server roles themselves (System Administrators and maybe Disk Administrators) to see who IS a member? Perhaps your users have administrative access through their network login account?
Sorry I can't be of more help, but this is getting into an area where I have less expertise. A network administrator might be able to help you out.