Results 1 to 2 of 2

Thread: User Mapping

  1. #1
    Join Date
    May 2009
    Posts
    11

    Unanswered: User Mapping

    Hi All:

    I am using Microsoft Sql 2005. In "Microsoft Sql Server Management Studio" when I clicked on the Security icon and click on Logins icon it gives me a list of user logins. For example for user login test2, when I right click on it and choose Properties. When I choose "User Mapping". I get the user "test2" Map to database "FORMS" and also in the below section called "Database role membership for: FORMS". It is mapped to the following role membership:

    db_accessadmin
    db_backupoperator
    db_datareader
    db_datawriter
    db_ddladmin
    db_denydatareader
    db_owner
    public

    Does anyone know how to create a sql script that will do that.
    When I right click the user "test2" and choose "script login as>create to > new query editor window>. It generates the script to create the user "test2" with the "server roles" mapped to it but not the "user mapping".

    Here is the script being generated:
    CREATE LOGIN [test2] WITH PASSWORD=N'test2', DEFAULT_DATABASE=[master],
    DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'sysadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'securityadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'serveradmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'setupadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'processadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'diskadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'dbcreator'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'bulkadmin'
    GO
    ALTER LOGIN [test2] DISABLE

    Yours,

    Frustrated.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is my latest version of script for doing this. I think it is ok but have not been using it in this form for long:
    Code:
    USE master
    GO
    
    DECLARE @clean_up_only      AS BIT
    
    --Set this to 1 to remove user and login, 0 to create
    SELECT  @clean_up_only      = 0
    
    BEGIN TRY
    
        --Delete the login if it exists and we are cleaning up
        IF EXISTS (SELECT NULL FROM sys.server_principals WHERE name = '<domain_name, VARCHAR(100), >\<windows_name, VARCHAR(100), >') 
        BEGIN
    
            IF @clean_up_only = 1
            BEGIN
                
                DROP LOGIN [<domain_name, VARCHAR(100), >\<windows_name, VARCHAR(100), >]
                
            END
    
        END
        --Otherwise create it
        ELSE IF @clean_up_only = 0
        BEGIN
    
            --Create the login
            CREATE LOGIN [<domain_name, VARCHAR(100), >\<windows_name, VARCHAR(100), >] FROM WINDOWS
    
        END
    
        USE [<database_name, VARCHAR(100), >]
    
        --Delete the user from database if we are cleaning up
        IF @clean_up_only = 1
        BEGIN
    
            IF EXISTS (SELECT NULL FROM sys.database_principals WHERE name = '<user_name, VARCHAR(100), >') 
            BEGIN
            
                DROP USER [<user_name, VARCHAR(100), >]
                
            END
            
        END
        ELSE
        IF @clean_up_only = 0 
        BEGIN
            
            --Check login does not already have another username associated with it
            IF EXISTS (SELECT NULL FROM sys.database_principals WHERE  sid     = SUSER_SID('<domain_name, VARCHAR(100), >\<windows_name, VARCHAR(100), >')
                                                                AND name    != '<user_name, VARCHAR(100), >')
            BEGIN
                
                RAISERROR
                    (
                        'The login [%s] already maps to another username in database [%s] - please investigate.'
                      , 16
                      , 1
                      , '<domain_name, VARCHAR(100), >\<windows_name, VARCHAR(100), >'
                      , '<database_name, VARCHAR(100), >'
                    )   WITH NOWAIT
                
            END
    
            --Add the user to <database_name, VARCHAR(100), > if required.
            IF NOT EXISTS (SELECT NULL FROM sys.database_principals WHERE name = '<user_name, VARCHAR(100), >') 
            BEGIN
                
                CREATE USER [<user_name, VARCHAR(100), >] FOR LOGIN [<domain_name, VARCHAR(100), >\<windows_name, VARCHAR(100), >]
                    
            END
    
            --Add the user to the <role_name, VARCHAR(100), > role if not already a member
            IF NOT EXISTS   (
                                SELECT  NULL
                                FROM    sys.database_role_members
                                INNER JOIN 
                                        sys.database_principals     AS roles
                                ON  roles.principal_id              = database_role_members.role_principal_id
                                INNER JOIN 
                                        sys.database_principals     AS members
                                ON  members.principal_id            = database_role_members.member_principal_id
                                WHERE   roles.name                  = '<role_name, VARCHAR(100), >'
                                    AND members.name                = '<user_name, VARCHAR(100), >'
                            )
            BEGIN
                
                EXEC sp_addrolemember 
                        @rolename       = '<role_name, VARCHAR(100), >'
                      , @membername     = '<user_name, VARCHAR(100), >'
            
            END
    
        END
        
    END TRY
    BEGIN CATCH
        
            DECLARE @l_error_message    AS NVARCHAR(2048)
                  , @l_error_severity   AS INT
                  , @l_error_state      AS INT 
            
            SELECT  @l_error_message    = ERROR_MESSAGE()
                  , @l_error_severity   = ERROR_SEVERITY()
                  , @l_error_state      = ERROR_STATE() 
                  
            RAISERROR   (
                            @l_error_message 
                          , @l_error_severity
                          , @l_error_state
                        ) WITH NOWAIT
        
    END CATCH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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