Script All Logins / Users / and Roles

  • Comments posted to this topic are about the item Script All Logins / Users / and Roles

  • Have made a few changes:

    Only looks at databases which are online, also added square brackets by the "USE" statements in case a database has an illegal character e.g. a number at the beginning of the name

    /****************************************************************

    This Script Generates A script to Create all Logins, Server Roles

    , DB Users and DB roles on a SQL Server

    Greg Ryan

    10/31/2013

    Cowboy DBA - 11/12/2013

    Only looks at databases which are online, also added square brackets by the

    "USE" statements in case a database has an illegal character e.g. a number at the beginning of the name

    ****************************************************************/

    SET NOCOUNT ON

    DECLARE @sql NVARCHAR(MAX)

    ,@Line INT = 1

    ,@max INT = 0

    ,@@CurDB NVARCHAR(100) = ''

    CREATE TABLE #SQL (

    Idx INT IDENTITY

    ,xSQL NVARCHAR(MAX))

    INSERT INTO #SQL

    (xSQL)

    SELECT 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + QUOTENAME(name) + ''')

    ' + ' CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD=' + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID=' + sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE=' + QUOTENAME(COALESCE(default_database_name,'master')) + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,'us_english')) + ', CHECK_EXPIRATION=' + CASE is_expiration_checked

    WHEN 1 THEN 'ON'

    ELSE 'OFF'

    END + ', CHECK_POLICY=' + CASE is_policy_checked

    WHEN 1 THEN 'ON'

    ELSE 'OFF'

    END + '

    Go

    '

    FROM sys.sql_logins

    WHERE name <> 'sa'

    INSERT INTO #SQL

    (xSQL)

    SELECT 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + QUOTENAME(name) + ''')

    ' + ' CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH ' + 'DEFAULT_DATABASE=' + QUOTENAME(COALESCE(default_database_name,'master')) + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,'us_english')) + ';

    Go

    '

    FROM sys.server_principals

    WHERE type IN ('U','G')

    AND name NOT IN ('BUILTIN\Administrators','NT AUTHORITY\SYSTEM');

    PRINT '/*****************************************************************************************/'

    PRINT '/*************************************** Create Logins ***********************************/'

    PRINT '/*****************************************************************************************/'

    SELECT @max-2 = MAX(idx)

    FROM #SQL

    WHILE @Line <= @max-2

    BEGIN

    SELECT @sql = xSql

    FROM #SQL AS s

    WHERE idx = @Line

    PRINT @sql

    SET @line = @line + 1

    END

    DROP TABLE #SQL

    CREATE TABLE #SQL2 (

    Idx INT IDENTITY

    ,xSQL NVARCHAR(MAX))

    INSERT INTO #SQL2

    (xSQL)

    SELECT 'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', ' + QUOTENAME(R.name) + ';

    GO

    '

    FROM sys.server_principals L

    JOIN sys.server_role_members RM

    ON L.principal_id = RM.member_principal_id

    JOIN sys.server_principals R

    ON RM.role_principal_id = R.principal_id

    WHERE L.type IN ('U','G','S')

    AND L.name NOT IN ('BUILTIN\Administrators','NT AUTHORITY\SYSTEM','sa');

    PRINT '/*****************************************************************************************/'

    PRINT '/******************************Add Server Role Members *******************************/'

    PRINT '/*****************************************************************************************/'

    SELECT @max-2 = MAX(idx)

    FROM #SQL2

    SET @line = 1

    WHILE @Line <= @max-2

    BEGIN

    SELECT @sql = xSql

    FROM #SQL2 AS s

    WHERE idx = @Line

    PRINT @sql

    SET @line = @line + 1

    END

    DROP TABLE #SQL2

    PRINT '/*****************************************************************************************/'

    PRINT '/*****************Add User and Roles membership to Indivdual Databases********************/'

    PRINT '/*****************************************************************************************/'

    --Drop Table #Db

    CREATE TABLE #Db (

    idx INT IDENTITY

    ,DBName NVARCHAR(100));

    INSERT INTO #Db

    SELECT name

    FROM sys.databases

    WHERE state_desc = 'ONLINE'

    AND name NOT IN ('master','model','msdb','tempdb')

    ORDER BY name

    SELECT @max-2 = MAX(idx)

    FROM #Db

    SET @line = 1

    --Select * from #Db

    --Exec sp_executesql @SQL

    WHILE @line <= @max-2

    BEGIN

    SELECT @@CurDB = DBName

    FROM #Db

    WHERE idx = @line

    SET @SQL = 'Use [' + @@CurDB + ']

    Declare @@Script NVarChar(4000) = ''''

    DECLARE cur CURSOR FOR

    Select ''Use [' + @@CurDB + '];

    Go

    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +

    mp.[name] + '''''')

    CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +

    ''GO'' + CHAR(13)+CHAR(10) +

    ''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']'''';

    Go''

    FROM sys.database_role_members a

    INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id

    INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id

    OPEN cur

    FETCH NEXT FROM cur INTO @@Script;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @@Script

    FETCH NEXT FROM cur INTO @@Script;

    END

    CLOSE cur;

    DEALLOCATE cur;';

    --Print @SQL

    EXEC sp_executesql @SQL;

    --Set @@Script = ''

    SET @Line = @Line + 1

    END

    DROP TABLE #Db

  • Very nice script.

  • Thanks for posting this. I just had to do a cold reinstall and restore of my DB server. Has to reconstruct the users/logins from memory! Now I can just run this periodically to have a backup/restore script.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Very nice contribution; a keeper! Thank you for sharing.

    Steve

  • Greg, Thanks for taking the time to create and share this script. I ran into a few issues though.

    1. The existence check: IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'[TestMe]'). The square brackets are treated as a literal and if 'TestMe' does exist, '[TestMe]' does not so the CREATE LOGIN portion will execute and fail.

    2. Same thing for the generated code to add a database user to a role. The user gets created in the database but the code generated to add it to a role: EXEC sp_addrolemember N'db_datareader', N'[TestMe]'; fails with error: User or role '[TestMe]' does not exist in this database.

    3. Will NOT generate code to add a database user IF the user is not assigned to a database role.

    4. Will NOT generate code to add a database user IF the database user name is NOT the same as the logon name.

    5. The code generated to add a database user does not seem to reflect the correct schema other than 'dbo'

    Thanks again for taking the time to share. Sorry if I sound critical.

    Lee

  • Nice script.

    Already mentioned the spaces in the database names. Fixable with brackets in the USE statements

    It has a [dbo] user issue.

    Tries to script this:

    CREATE USER [dbo] FOR LOGIN [dbo] WITH DEFAULT_SCHEMA=[dbo];

    Which does not make sense, where there is not an explicit dbowner assigned.

    Have to exclude this 'dbo' user in the internal cursor join:

    where mp.[name]<>''dbo''

    Then it looks to be working

    Thanks

    Alex Donskoy

    SQL Server DBA

    Greenberg Traurig PA

    Miami FL

  • Why not use sp_helprevlogin to script the logins/users instead? http://support.microsoft.com/kb/918992

    Also, any reason why you decided to not include all permissions and only script roles?

  • Hi.

    I am using SQL Server 2008 R2.

    I get the following error when I run the script.

    -----------

    Msg 207, Level 16, State 1, Line 54

    Invalid column name 'idx'.

    Msg 207, Level 16, State 1, Line 63

    Invalid column name 'idx'.

    -----------

    When setting up the "Collection" section of the "Case insensitive" is set.

    That is what I thought.

    How do I run the script without error ?

    Thanks.

  • Is there an option to just limit logins to one database. The reason I am asking because we usually refresh QA database from PROD database. We want to capture current logins for the QA database, overwrite it from PROD DB backup, and then re-apply QA permissions.

    Thanks!

  • ggeier (11/14/2013)


    Why not use sp_helprevlogin to script the logins/users instead? http://support.microsoft.com/kb/918992

    Also, any reason why you decided to not include all permissions and only script roles?

    sp_helprevlogin doesn't do users; just logins and server roles. The assumption must be that you're coming onto a new server where the user DBs don't yet exist.

    Ken

  • Wish I read the comment about adding Square brackets before I did the same

  • Good script, thanks.

  • I made a few more changes. The script now adds grants for each database and disables logins that are disabled. I also removed all temp tables and cursors and installed table variables.

Viewing 15 posts - 1 through 15 (of 18 total)

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