Unanswered: how to tell if a login has already been created
I want to create a script that will run and create a predetermined set of logins AND dbrolemembership but only if they do not presently exist in the database. I have played around with sp_addlogin and sp_addrolemember. I then checked the sysusers and syslogins table to determine the existence. I cannot get a row to insert into sysusers. Can someone elighten me on where the login & rolemembership is stored. I also spent some time looking for a proc that might give me what i need.
Role membership is stored in sysusers table of each database (except for tempdb) and login info in master.dbo.syslogins. You right on track based on sp's and tables you mentioned. All you need to do is script those sp's (sp_addlogin and sp_addrolemember) in your QA and you'll have all the answers yourself.
I just ran your script and replaces Main with my database, and when I do a SELECT from sysusers of that database I see both users added. Did I miss anything?
And of course, you'd do something like "if not exist (select1 from master.dbo.syslogins where name = <new_login>) exec sp_addlogin <...>" to validate the presence or abscence of a login, same with users, etc.