balasach82 (2/28/2012)
1. For Extended SP's, I go to Database Properties->Security-->
Roles-->Database Roles--> Public--> Properties-->Securables
Here, I see a number of Extended SP's. I had to select each one and then remove EXECUTE persmission; and this has to be done for all user db's. So, is there a way to do this either by query or GUI to revoke execution permission for all Extended SP's?
2. For Public role restriction, In Server role public--> Properties-->Permissions -->i see tcpip, named pipes etc only with CONNECT option enabled. So, if i need to remove all permissions for Public, should i go use Server role public or Database role public? In DB role public, as said above, it has execute permissons for most sp, connect etc
About 1) In my user databases the public role only has permissions on some systemviews, but no extended procedures so I suspect someone added these permissions in the model database or after creating of a user database.
About 2) the permissions you name are necessary for a login to connect to the server.
You ask which permissions you need to remove, but that really depends on your requirements. As I stated earlier, by default the public role (server or database) only has the permissions which are necessary to connect and view certain server and database objects. If your public role has more permissions I would first investigate why that is the case. Has someone added these permission in the model database, so all new user databases will have them or is it only in certain user databases. In the latter case maybe some application is depending on it?
[font="Verdana"]Markus Bohse[/font]