Post reply

Assign all Logins to a Database (SQL 2005)

  • April 9, 2008 at 4:05 pm

    #185127

    I'm looking for direction as to how to assign all users with a Login in SQL to a Database. Any advise where to get started would be appreciated. I found some great articles as to how to transfer a database while keeping the users in tact...however at this time I need to get each user assigned to the database.

    Much thanks in advance.

  • April 9, 2008 at 9:31 pm

    #800740

    You're looking to take all the logins for a particular server and making each login a user for the database? Is that correct? In other words, if you have 17 logins to a particular SQL Server, you want those 17 logins to be made users in the database?

    K. Brian Kelley
    @kbriankelley

  • April 11, 2008 at 4:10 pm

    #801743

    that is correct.

  • April 11, 2008 at 11:15 pm

    #801775

    You can easily write a script to create a script. Something along the lines of:

    SELECT 'CREATE USER [' + sp1.name + '] FROM LOGIN [' + sp1.name + '];'

    FROM [sys].server_principals sp1

    WHERE sp1.TYPE IN ('S', 'G', 'U')

    AND sp1.NAME NOT LIKE '%SQLServer2005%'

    AND sp1.principal_id NOT IN

    (SELECT srm.member_principal_id

    FROM [sys].server_role_members srm

    JOIN [sys].server_principals sp2

    ON srm.role_principal_id = sp2.principal_id

    WHERE sp2.NAME = 'sysadmin');

    K. Brian Kelley
    @kbriankelley

  • April 17, 2008 at 8:08 am

    #803735

    Thanks, that gives me a starting point. My SQL skills are a little old...time to get them back into gear.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply