Verify SQL Server Login and Login Settings

,

This script verifies that a specified login name:

- exists (creates it if it does not exist)

- has the correct password (resets it)

- is a member of the sysadmin login role (joins if not a member)

 

and checks that the database users that should be associated:

- exist (creates them if they do not exist)

- are mapped to the login with specified permissions

 

Adjust database name(s) in @db_str, adding database names if needed. These database names are the databases you want the script to check/associate with the login.

Adjust the login name and password using @login and @password.

Adjust the default database name using @default_db.

Adjust the owner of the databases using @db_owner (I recommend 'sa' or other sysadmin account so that you can be assured that the login properties can be edited easily later.

Adjust the database role membership using @db_role. You may change this setting and re-run the script for multiple database role memberships.

Adjust the server role membership using @srv_role. This can be used similarly to @db_role.

/*
* Script to verify specified login and settings.
* 7-11-2011 by Seth Delconte
*
*/
DECLARE @db_str VARCHAR(100)
DECLARE @login VARCHAR(50)
DECLARE @password VARCHAR(50)
DECLARE @default_db VARCHAR(50)
DECLARE @db_owner VARCHAR(50)
DECLARE @db_role VARCHAR(50)
DECLARE @srv_role VARCHAR(50)
SET @db_str = '''db01'',''db02'',''db03'''    	--CHANGE DATABASE NAMES HERE
SET @login = 'Seth'                            		--CHANGE LOGIN HERE
SET @password = '''P@$$w0rd'''                		--CHANGE PASSWORD HERE
SET @default_db = 'master'                    		--CHANGE DEFAULT DB HERE
SET @db_owner = 'sa'                        		--CHANGE OWNER HERE
SET @db_role = 'db_owner'                    		--CHANGE DB ROLE HERE
SET @srv_role = '''sysadmin'''                    	--CHANGE SERVER ROLE HERE

IF (SELECT IS_SRVROLEMEMBER('sysadmin'))=0
    BEGIN
        print 'You are not a sysadmin.'
        RETURN                        
    END
ELSE
    BEGIN
        DECLARE @sql1 VARCHAR(MAX)
        DECLARE @sql2 VARCHAR(MAX)
        DECLARE @lgn VARCHAR(50)
        DECLARE @role VARCHAR(50)
        DECLARE @owner VARCHAR(50)
        SET @lgn = ''''+@login+'''' --to account for extra parends
        SET @role = ''''+@db_role+'''' --to account for extra parends
        SET @owner = ''''+@db_owner+'''' --to account for extra parends
        SET @sql1='EXECUTE ('' BEGIN TRY
                                DECLARE @pass VARCHAR(100)
                                SET @pass = '''+@password+'''
                                IF NOT EXISTS(SELECT name FROM master.dbo.syslogins WHERE name = '''+@lgn+''')
                                    BEGIN
                                        print ''''Creating login '+@login+'...''''
                                        CREATE LOGIN ['+@login+'] WITH PASSWORD='''+@password+''', DEFAULT_DATABASE=['+@default_db+'], 
                                            DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
                                        EXEC sp_addsrvrolemember @loginame = '''+@lgn+''', @rolename = '''+@srv_role+'''
                                    END
                                ELSE
                                    BEGIN
                                        print ''''Changing password to ''''+@pass+'''' for login '+@login+'...''''
                                        print ''''Changing default database to '+@default_db+' for login '+@login+'...''''
                                        ALTER login '+@login+' WITH PASSWORD = '''+@password+''', DEFAULT_DATABASE=['+@default_db+']
                                    END
                            END TRY
                            BEGIN CATCH
                                print ERROR_MESSAGE()+'''' Rolling back...''''
                                IF @@TRANCOUNT > 0
                                    ROLLBACK
                            END CATCH
                        '')'

        SET @sql2='
        IF (''?'' IN ('+@db_str+'))
            BEGIN
                print ''For ?:''
                EXECUTE ('' BEGIN TRY
                                DECLARE @pass VARCHAR(100)
                                SET @pass = '''+@password+'''
                                USE [?]
                                print ''''Changing ? database ownership to '+@db_owner+'...''''
                                EXEC dbo.sp_changedbowner @loginame = '''+@owner+'''
                                IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = '''+@lgn+''')
                                    BEGIN
                                        CREATE USER ['+@login+'] FOR LOGIN ['+@login+'] 
                                        print ''''User '+@login+' created.''''
                                    END
                                ELSE
                                    BEGIN
                                        print ''''User '+@login+' exists already.''''
                                    END
                                print ''''(Re)Associating user '+@login+' with login '+@login+'...''''
                                EXEC dbo.sp_change_users_login ''''UPDATE_ONE'''','''+@lgn+''','''+@lgn+'''
                                print ''''Adding user '+@login+' to the '+@db_role+' role...''''
                                EXEC sp_addrolemember '''+@role+''','''+@lgn+'''
                            END TRY
                            BEGIN CATCH
                                print ERROR_MESSAGE()+'''' Rolling back...''''
                                IF @@TRANCOUNT > 0
                                    ROLLBACK
                            END CATCH
                        '')
                print ''Done with ?.''
            END'
            
            EXEC (@sql1)
            EXEC sp_msforeachdb @sql2
            print ''
            print 'Script finished.'
    END


    

Rate

3 (2)

Share

Share

Rate

3 (2)