Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72

    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.

    any direction or comments are appreciated.

    Mike

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  3. #3
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72
    Thanks for the response.

    I have written the scritps to create the users and add them to the correct roles. I would like to test to see if the user and /or role(s) are already existing when I run the script.

    PRINT '===> Add logins'
    EXEC sp_addlogin 'pubuser','pubuser'
    EXEC sp_addlogin 'opsuser','opsuser'

    PRINT '===> Add user, attach login and assign permissions for users'
    USE Main
    EXEC sp_grantdbaccess 'pubuser'
    EXEC sp_addrolemember 'db_datareader','pubuser'
    EXEC sp_addrolemember 'db_datawriter','pubuser'

    EXEC sp_grantdbaccess 'opsuser'
    EXEC sp_addrolemember 'db_datareader','opsuser'
    EXEC sp_addrolemember 'db_datawriter','opsuser'


    The issue / question is when I select * from syslogins I can see the pubuser and opsuser. However, I cannot see a cooresponding entry on sysusers. Still missing a piece somewhere?

    mike

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  5. #5
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72
    I was using the master sysusers table.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •