Post reply

script login mappings

  • July 30, 2009 at 12:11 pm

    #207521

    How would i script all mappings of a login so that i can run the same script on a new login which i create.

    thanks

  • July 30, 2009 at 12:28 pm

    #1032603

    Are you talking about creating a template script to create logins?

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • July 30, 2009 at 12:36 pm

    #1032609

    No. I have a login in which i have so many dabase mappings out of which that login has different preveliages on different tables and now i have to create another login with the same mappings and looking ofr an easy way to do.

  • July 30, 2009 at 12:46 pm

    #1032612

    Open up SSMS. Right-click the account in question. Script Login as >> CREATE To >> New Query Editor window.

    Then you will just need to change the entries of the login (use find and replace is quick way) and give it a password.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • July 30, 2009 at 12:51 pm

    #1032618

    I know that but that just creates login not the mappings to different databases

  • July 30, 2009 at 12:55 pm

    #1032624

    Open the properties window of the user and go to user mappings. You will probably need to make change to the properties (like check a database and then uncheck it). Then click on the script button to send to a query. It will then give you all the commands for the database mappings.

    Then combine that will the other script.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • July 30, 2009 at 3:41 pm

    #1032723

    I think instead of doing that i can map manually, its the same but i am looking for something easy to use.

  • July 31, 2009 at 8:09 am

    #1032950

    There was a great script developed by Shivaram Challa, http://www.sqlservercentral.com/scripts/Administration/63841/, which I used to get a listing of all DBs (i.e., mappings) for a individual user.

    You can then use the same data from the that script to add a user to all DBs in the list. I would probably take Shivaram's script, create a lookup table instead of a temp table, then script the creation of the new user and use a modified version of this script:

    /* =============================================

    Author:John R.

    Create date: 11/7/2007

    Description: Set Permissions all objects Role_User

    Code will only Add Users to databases that begin with DB (i.e., DBDev, DBProd, DB1, etc.) and add users to the Role_User role.

    ============================================= */

    sp_msforeachdb 'USE ?;DECLARE @DBNameVar NVARCHAR(128); if (LEFT((select db_name()),2) = ''DB'') BEGIN;

    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Role_User'' AND type = ''R'') BEGIN;

    DECLARE @Statement NVARCHAR(2000);

    DECLARE @newuser NVARCHAR(50);

    SET @newuser = ''USERNAMEHERE''

    SET @Statement = N''IF NOT EXISTS (SELECT * FROM ?.sys.database_principals WHERE name = ''''''+@NewUser+'''''')

    CREATE USER [''+@NewUser+''] FOR LOGIN [''+@NewUser+''] WITH DEFAULT_SCHEMA=[dbo]'';

    --Print @Statement;

    EXEC sp_executesql @Statement;

    SET @Statement = N''sp_addrolemember ''''Role_User'''', ''''''+@NewUser+'''''''';

    --Print @Statement;

    EXEC sp_executesql @Statement;

    END;END;

    '

    I know you said you wanted something "simple", but sometimes you need to do the ground work and the coding to get to that final "simple" solution that meets your specific requirements. 😉

    Hope this helps you on your journey.

    Good luck! 🙂

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

Viewing 8 posts - 1 through 7 (of 7 total)

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