Need help with my query

I am trying to setup a script that I will be able to delete certain logins that is tied to a certain database role member. Right now I am creating a script to run using a cursor. but it seems to be not working when trying to use an sp_revokedbaccess '@loginname' along with the select statment. How do I do this?

velodba sent the above to me in a private message. I felt it gave a little more insight into what was trying to be done.

I have a couple of questions though.

You are trying to delete all logins that are assigned to a particular database role?

If that is the case, I don't understand the part about pulling all the database roles. Why pull them all if you are only interested in one?

If you can clarify the problem, I'm sure we can come up with an answer....

Thanks for the quick response. In answering your question and to clarify what I am trying to do. I have been requested to delete all non-essential logins from a server. The thing that is having me think to create a script is that everytime that server is reloaded, those logins are set back on to that server. I need to create a script that will allow me to delete multiple logins that are tied to a certain database role. I just don't know how to do that with out having to go through each individual login and deleteing them manually.What would be the most efficient and quicker way to do this?

Ok, well I don't have a sql server 7 installation here to test this with, but I do have a 2000 installation. Check this out, run "SELECT * FROM sysusers" on one of your databases. This should give you a list of users for that database. It also gives you what roles they are in. Then, all you need to do is delete users where they are part of a certain role:

delete from sysusers where roles='0x000002'

Hope that gives you some ideas..