Script Logins

  • Hi All

    Is there any way to script out all the Logins on a SQL Server instance, including server level privileges, mapped databases and permissions on the databases?

    I have used sp_help_revlogin from Microsoft on many occasions but that doesn't do everything I need.

    Thanks

  • I have done 3 SQL Server migrations from obsolete hardware to new over the past 6 months and have used sp_help_revlogin with little trouble to move the users and permissions over. (knock on wood). The only problem we had was some strange permissions stuff burried in msdb for some sharepoint logins, that I'm struggling to remember the details for.

    The targets were always brand new clean installations.

    What sort of trouble did you run into previously? (I'll search my notes and see what the details of the SharePoint service migration piece was)

  • I've had no problems with the sp_help_revlogin procedure

    What I need is a script that will script out everything in the sp_help_revlogin procedure and include server level permissions as well as database mappings

    Thanks

  • I think this may be what you are looking for. It is a script I wrote to do Instance Security Audit documentation. This is the latest version. Set the @outputtype variable to 1 for columnar (report style) listings, and 2 for the actual assignment statements.

    Hope it helps.

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

    /* = Instance Security Audit Documentation = */

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

    /*Created Date: 12/28/2011

    By: VikingDBA

    Modifications:

    08/07/2012 Changed to also do Server Level Permissions (like VIEW ANY DATABASE, or VIEW SERVER STATE)

    01/09/2013 Changed to print the scripts to create the CREATE ROLE and CREATE USER statements

    01/31/2013 Changed to print the server level permission statements, and statements to set the default

    databases for users

    Dependencies:

    This script depends on the following to exist:

    none

    Summary:

    This script creates documentation for all databases in an instance, including the server level permissions, database role

    permissions, and individual database object permissions.

    Note that changing @outputtype = 1 sets for columnar (report style) and value of 2 creates the assignment statements

    NOTE: Runs for all databases, unless a specific database is used in the WHERE clause below.

    Also, see User Settable Variables section below to see if any variables need to be set.

    */

    Use MASTER

    SET NOCOUNT ON

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

    -- Security Audit for SERVER Roles

    DECLARE @sr varchar(100)

    DECLARE @mn varchar(150)

    DECLARE @cmd varchar(4000)

    DECLARE @col1nm varchar(200)

    DECLARE @col2nm varchar(200)

    DECLARE @col3nm varchar(200)

    DECLARE @col4nm varchar(200)

    DECLARE @col5nm varchar(200)

    DECLARE @col6nm varchar(200)

    DECLARE @col7nm varchar(200)

    DECLARE @col8nm varchar(200)

    DECLARE @col9nm varchar(200)

    DECLARE @col10nm varchar(200)

    DECLARE @col11nm varchar(200)

    DECLARE @col12nm varchar(200)

    DECLARE @col13nm varchar(200)

    DECLARE @col14nm varchar(200)

    DECLARE @col15nm varchar(200)

    DECLARE @col16nm varchar(200)

    DECLARE @col17nm varchar(200)

    DECLARE @col18nm varchar(200)

    DECLARE @col19nm varchar(200)

    DECLARE @col20nm varchar(200)

    DECLARE @col1len int

    DECLARE @col2len int

    DECLARE @col3len int

    DECLARE @col4len int

    DECLARE @col5len int

    DECLARE @col6len int

    DECLARE @col7len int

    DECLARE @col8len int

    DECLARE @col9len int

    DECLARE @col10len int

    DECLARE @col11len int

    DECLARE @col12len int

    DECLARE @col13len int

    DECLARE @col14len int

    DECLARE @col15len int

    DECLARE @col16len int

    DECLARE @col17len int

    DECLARE @col18len int

    DECLARE @col19len int

    DECLARE @col20len int

    DECLARE @col1max int

    DECLARE @col2max int

    DECLARE @col3max int

    DECLARE @col4max int

    DECLARE @col5max int

    DECLARE @col6max int

    DECLARE @col7max int

    DECLARE @col8max int

    DECLARE @col9max int

    DECLARE @col10max int

    DECLARE @col11max int

    DECLARE @col12max int

    DECLARE @col13max int

    DECLARE @col14max int

    DECLARE @col15max int

    DECLARE @col16max int

    DECLARE @col17max int

    DECLARE @col18max int

    DECLARE @col19max int

    DECLARE @col20max int

    DECLARE @col1min int

    DECLARE @col2min int

    DECLARE @col3min int

    DECLARE @col4min int

    DECLARE @col5min int

    DECLARE @col6min int

    DECLARE @col7min int

    DECLARE @col8min int

    DECLARE @col9min int

    DECLARE @col10min int

    DECLARE @col11min int

    DECLARE @col12min int

    DECLARE @col13min int

    DECLARE @col14min int

    DECLARE @col15min int

    DECLARE @col16min int

    DECLARE @col17min int

    DECLARE @col18min int

    DECLARE @col19min int

    DECLARE @col20min int

    DECLARE @rn varchar(200)

    DECLARE @un varchar(200)

    DECLARE @ut varchar(200)

    DECLARE @sd varchar(200)

    DECLARE @pn varchar(200)

    DECLARE @SN varchar(200)

    DECLARE @on varchar(200)

    DECLARE @pd varchar(200)

    DECLARE @sdmax int

    DECLARE @pnmax int

    DECLARE @snmax int

    DECLARE @onmax int

    DECLARE @pdmax int

    DECLARE @unmax int

    DECLARE @rnmax int

    DECLARE @utmax int

    DECLARE @outputtypeint

    DECLARE @prodlevel varchar(25)

    DECLARE @versionvarchar(250)

    DEClARE @prodvervarchar(50)

    DECLARE @editionvarchar(50)

    DECLARE @includeobjlvlpermsbit

    DECLARE @includeroleinfobit

    DECLARE @includedefaultdbbit

    DECLARE @usnmvarchar(128)

    DECLARE @ustpvarchar(60)

    DECLARE @stdscvarchar(60)

    DECLARE @permnm varchar(128)

    DECLARE @collationnamevarchar(200)

    DECLARE @linevalvarchar(2000)

    DECLARE @loginname varchar(100)

    DECLARE @dbnametouse sysname

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

    -- User Settable Variables

    SET @outputtype = 1-- 1=columnar 2=assignment statements

    SET @includeobjlvlperms = 1

    SET @includeroleinfo = 1

    SET @includedefaultdb = 1

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

    SELECT @prodlevel=CONVERT(varchar(25),SERVERPROPERTY('ProductLevel'))

    SELECT @version=CONVERT(varchar(250),@@VERSION)

    SELECT @prodver=CONVERT(varchar(50),SERVERPROPERTY('ProductVersion'))

    SELECT @edition=CONVERT(varchar(50),SERVERPROPERTY('Edition'))

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

    --Find split out line

    DECLARE @lvaltousevarchar(2000)

    DECLARE @lvallengthint

    DECLARE @lvalctint

    DECLARE @spotcatint

    DECLARE @spotcatvalint

    DECLARE @lval1varchar(2000)

    DECLARE @lval2varchar(2000)

    DECLARE @lval3varchar(2000)

    DECLARE @lval4varchar(2000)

    DECLARE @lval5varchar(2000)

    DECLARE @lval6varchar(2000)

    SET @lvaltouse = @version

    SET @lvallength = LEN(@lvaltouse)

    SET @lvalct = 1

    SET @spotcat = 1

    SET @lval1 = ''

    SET @lval2 = ''

    SET @lval3 = ''

    SET @lval4 = ''

    SET @lval5 = ''

    SET @lval6 = ''

    WHILE @spotcat <= @lvallength

    BEGIN

    SET @spotcatval = ASCII(SUBSTRING(@lvaltouse,@spotcat,1))

    if @spotcatval = 10-- value we are looking for

    SET @lvalct = @lvalct + 1-- set to go to the next line and start building it

    else-- add to current value line

    BEGIN

    if @spotcatval <> 9-- values we are wanting to exclude

    BEGIN

    if @lvalct = 1

    SET @lval1 = @lval1 + CHAR(@spotcatval)

    if @lvalct = 2

    SET @lval2 = @lval2 + CHAR(@spotcatval)

    if @lvalct = 3

    SET @lval3 = @lval3 + CHAR(@spotcatval)

    if @lvalct = 4

    SET @lval4 = @lval4 + CHAR(@spotcatval)

    if @lvalct = 5

    SET @lval5 = @lval5 + CHAR(@spotcatval)

    if @lvalct = 6

    SET @lval6 = @lval6 + CHAR(@spotcatval)

    END

    END

    SET @spotcat = @spotcat + 1

    END

    --PRINT 'Line to split=' + @lvaltouse

    --PRINT 'line1 = ' + @lval1

    --PRINT 'line2 = ' + @lval2

    --PRINT 'line3 = ' + @lval3

    --PRINT 'line4 = ' + @lval4

    --PRINT 'line5 = ' + @lval5

    --PRINT 'line6 = ' + @lval6

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

    CREATE TABLE #dummyuserassign

    (RecID int IDENTITY,

    LineValvarchar(2000)

    )

    PRINT '============================================================================================================='

    PRINT ' Security Audit For Server Instance ' + CONVERT(varchar(128),@@servername)

    if @outputtype = 2

    PRINT ' Assignment Statements'

    PRINT ' For ' + CONVERT(varchar(128),getdate(),101) + ' ' + CONVERT(varchar(128),getdate(),108)

    PRINT '============================================================================================================='

    PRINT 'SQL Server Version: ' + @lval1

    PRINT ' ' + @lval4

    PRINT '============================================================================================================='

    PRINT 'NOTE: Make sure to get list of logins using the sp_help_revlogin stored procedure in the master database.'

    PRINT '============================================================================================================='

    PRINT ' Server Role Security Settings'

    PRINT ' '

    PRINT ' '

    CREATE TABLE #rolememberdummy

    (ServerRole varchar(100),

    MemberName varchar(150),

    MemberSID varchar(2000)

    )

    CREATE TABLE #dummyDBPerms

    (StateDescvarchar(200),

    PermNamevarchar(200),

    SchemaNamevarchar(200),

    ObjectNamevarchar(200),

    UserNamevarchar(200),

    ObjectTypevarchar(200),

    UserTypevarchar(200)

    )

    -- Security Audit

    INSERT INTO #rolememberdummy

    EXEC sp_helpsrvrolemember 'sysadmin'

    INSERT INTO #rolememberdummy

    EXEC sp_helpsrvrolemember 'securityadmin'

    INSERT INTO #rolememberdummy

    EXEC sp_helpsrvrolemember 'serveradmin'

    INSERT INTO #rolememberdummy

    EXEC sp_helpsrvrolemember 'dbcreator'

    INSERT INTO #rolememberdummy

    EXEC sp_helpsrvrolemember 'diskadmin'

    INSERT INTO #rolememberdummy

    EXEC sp_helpsrvrolemember 'processadmin'

    INSERT INTO #rolememberdummy

    EXEC sp_helpsrvrolemember 'setupadmin'

    INSERT INTO #rolememberdummy

    EXEC sp_helpsrvrolemember 'bulkadmin'

    SET @col1nm = 'Role'

    SET @col1len = 20

    SET @col2nm = ''

    SET @col2len = 8

    SET @col3nm = 'Member Name'

    SET @col3len = 30

    PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm

    PRINT REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len)

    --SELECT CONVERT(varchar(30),ServerRole) as ServerRole, CONVERT(varchar(30),MemberName) AS MemberName FROM #rolememberdummy

    DECLARE backupFiles CURSOR FOR

    SELECT ServerRole, MemberName FROM #rolememberdummy

    OPEN backupFiles

    -- Loop through all the files for the database

    FETCH NEXT FROM backupFiles INTO @sr, @mn

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @col1nm = @sr

    SET @col1len = 20

    SET @col2nm = ''

    SET @col2len = 8

    SET @col3nm = @mn

    SET @col3len = 30

    PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm

    FETCH NEXT FROM backupFiles INTO @sr, @mn

    END

    CLOSE backupFiles

    DEALLOCATE backupFiles

    DROP TABLE #rolememberdummy

    PRINT ' '

    PRINT ' '

    PRINT '==========================================================================================================='

    PRINT ' Server Level Permissions'

    PRINT ' '

    PRINT ' '

    CREATE TABLE #serverpermdummy

    (UserName varchar(128),

    UserType varchar(60),

    StateDesc varchar(60),

    PermName varchar(128)

    )

    INSERT INTO #serverpermdummy

    SELECT l.name as UserName, l.type_desc AS UserType, p.state_desc AS StateDesc, p.permission_name AS PermName

    FROM sys.server_permissions AS p

    JOIN sys.server_principals AS l ON p.grantee_principal_id = l.principal_id

    WHERE ((permission_name <> 'CONNECT SQL' AND permission_name <> 'CONNECT') OR p.state_desc = 'DENY') AND l.type_desc <> 'CERTIFICATE_MAPPED_LOGIN' AND l.name NOT LIKE '%##MS_%'

    ORDER BY l.principal_id

    --SELECT * FROM sys.server_principals

    SET @col1nm = 'User Name'

    SET @col1len = 20

    SET @col2nm = ''

    SET @col2len = 8

    SET @col3nm = 'User Type'

    SET @col3len = 20

    SET @col4nm = ''

    SET @col4len = 8

    SET @col5nm = 'State Desc'

    SET @col5len = 20

    SET @col6nm = ''

    SET @col6len = 8

    SET @col7nm = 'Permission'

    SET @col7len = 30

    SET @col1min = LEN(@col1nm)

    SET @col3min = LEN(@col3nm)

    SET @col5min = LEN(@col5nm)

    SET @col7min = LEN(@col7nm)

    --Get the length of the longest occurance of the columns

    SELECT @col1max = ISNULL(MAX(len(LTRIM(RTRIM(UserName)))),0) FROM #serverpermdummy

    SELECT @col3max = ISNULL(MAX(len(LTRIM(RTRIM(UserType)))),0) FROM #serverpermdummy

    SELECT @col5max = ISNULL(MAX(len(LTRIM(RTRIM(StateDesc)))),0) FROM #serverpermdummy

    SELECT @col7max = ISNULL(MAX(len(LTRIM(RTRIM(PermName)))),0) FROM #serverpermdummy

    --Set some minimum values so column doesn't print short

    if @col1max < @col1min SET @col1len = @col1min else SET @col1len = @col1max

    if @col3max < @col3min SET @col3len = @col3min else SET @col3len = @col3max

    if @col5max < @col5min SET @col5len = @col5min else SET @col5len = @col5max

    if @col7max < @col7min SET @col7len = @col7min else SET @col7len = @col7max

    if @outputtype = 1

    BEGIN

    PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len) + @col7nm + SPACE(@col7len-len(@col7nm))

    PRINT REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len) + SPACE(@col4len) + REPLICATE('=',@col5len) + SPACE(@col6len) + REPLICATE('=',@col7len)

    END

    else

    if EXISTS (SELECT ISNULL(UserName,'') AS UserName, ISNULL(UserType,'') AS UserType, ISNULL(StateDesc,'') AS StateDesc, ISNULL(PermName,'') AS PermName FROM #serverpermdummy)

    PRINT 'USE master;'

    DECLARE backupFiles CURSOR FOR

    SELECT ISNULL(UserName,'') AS UserName, ISNULL(UserType,'') AS UserType, ISNULL(StateDesc,'') AS StateDesc, ISNULL(PermName,'') AS PermName FROM #serverpermdummy

    OPEN backupFiles

    -- Loop through all the files for the database

    FETCH NEXT FROM backupFiles INTO @usnm, @ustp, @stdsc, @permnm

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @col1nm = @usnm

    SET @col2nm = ''

    SET @col3nm = @ustp

    SET @col4nm = ''

    SET @col5nm = @stdsc

    SET @col6nm = ''

    SET @col7nm = @permnm

    if @outputtype = 1

    PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len) + @col7nm + SPACE(@col7len-len(@col7nm))

    else

    PRINT @stdsc + ' ' + @permnm + ' TO ' + @usnm + ';'

    FETCH NEXT FROM backupFiles INTO @usnm, @ustp, @stdsc, @permnm

    END

    CLOSE backupFiles

    DEALLOCATE backupFiles

    DROP TABLE #serverpermdummy

    PRINT ' '

    PRINT ' '

    PRINT '==========================================================================================================='

    PRINT ' Information By Database'

    PRINT ' '

    PRINT ' '

    CREATE TABLE #DummyDBDesc

    (RecIDintIDENTITYNOT NULL,

    ServerNamevarchar(128)NULL,

    DBNamevarchar(100)NULL,

    RecoveryModelvarchar(10)NULL,

    CompatibilityLevelvarchar(30)NULL,

    ReadWriteDescvarchar(10)NULL

    )

    CREATE TABLE #dummyDBRoles

    (RoleNamevarchar(200),

    UserNamevarchar(200),

    UserTypevarchar(200)

    )

    CREATE TABLE #dummyrolelist

    (RoleNamevarchar(200)

    )

    CREATE TABLE #dummyDBUsers

    (UserNamevarchar(200),

    UserTypevarchar(200)

    )

    INSERT INTO #DummyDBDesc

    select CONVERT(varchar(128),@@servername) AS ServerName, CONVERT(varchar(100),name) as DBName, CONVERT(varchar(10),recovery_model_desc) as RecoveryModel,--database_id,

    CASE compatibility_level

    WHEN 80 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2000 *'

    WHEN 90 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2005'

    WHEN 100 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2008'

    WHEN 105 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2008 R2'

    WHEN 110 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2012'

    ELSE CONVERT(varchar(4),compatibility_level)

    END AS CompatibilityLevel,

    CASE is_read_only

    WHEN 0 THEN CONVERT(varchar(10),'RW')

    ELSE CONVERT(varchar(10),'R')

    END as ReadWriteDesc

    FROM sys.databases

    WHERE name NOT IN('tempdb','master','msdb','model') and name NOT LIKE '%ReportServer%' AND state = 0

    --AND name = 'MyDatabase'

    ORDER BY name

    DECLARE backupFiles CURSOR FOR

    SELECT DBName, RecoveryModel, CompatibilityLevel, ReadWriteDesc FROM #DummyDBDesc ORDER BY DBName

    OPEN backupFiles

    DECLARE @DBN varchar(100)

    DECLARE @rm varchar(10)

    DECLARE @cl varchar(30)

    DECLARE @rwd varchar(10)

    -- Loop through all the files for the database

    FETCH NEXT FROM backupFiles INTO @DBN, @rm, @cl, @rwd

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'Database Name : ' + @DBN

    PRINT 'Recovery Model : ' + @rm

    PRINT 'Compatibility Level: ' + @cl

    PRINT 'Read/Write : ' + @rwd

    PRINT ' '

    PRINT ' '

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

    /* Database User Information */

    --Start with a clean table to load the values

    TRUNCATE TABLE #dummyDBUsers

    -- Get roles for this database and load into the temp table

    SET @cmd = 'USE [' + @DBN + ']; INSERT INTO #dummyDBUsers SELECT CONVERT(varchar(100),name) AS UserName, CONVERT(varchar(100),type_desc) as UserType FROM sys.database_principals WHERE (type = ''S'' OR type = ''U'' OR type = ''G'') AND is_fixed_role = 0 AND (name NOT IN (''guest'',''dbo'',''INFORMATION_SCHEMA'',''sys''))'

    --PRINT @cmd

    EXEC (@cmd)

    --Get the length of the longest occurance of the columns

    SELECT @unmax = ISNULL(MAX(len(UserName)),0) FROM #dummyDBUsers

    SELECT @utmax = ISNULL(MAX(len(UserType)),0) FROM #dummyDBUsers

    --Set some minimum values so column doesn't print short

    if @unmax < 25 SET @unmax = 25

    if @utmax < 25 SET @utmax = 25

    --Set and print the column headings for the role information

    SET @col1nm = 'UserName'

    SET @col1len = @unmax

    SET @col2nm = ''

    SET @col2len = 5

    SET @col3nm = 'UserType'

    SET @col3len = @utmax

    PRINT ' '

    PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm))

    PRINT SPACE(10) + REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len)

    DECLARE backupFiles2 CURSOR FOR

    SELECT UserName, UserType FROM #dummyDBUsers ORDER BY UserName

    OPEN backupFiles2

    -- Loop through all the files for the database

    FETCH NEXT FROM backupFiles2 INTO @un, @ut

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --Set and print the row details for the role information

    SET @col1nm = SUBSTRING(@un,1,@unmax)

    SET @col3nm = SUBSTRING(@ut,1,@utmax)

    PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm))

    FETCH NEXT FROM backupFiles2 INTO @un, @ut

    END

    CLOSE backupFiles2

    DEALLOCATE backupFiles2

    PRINT ' '

    PRINT ' '

    if @outputtype = 2-- create the statements to assign a user to this database

    BEGIN

    TRUNCATE TABLE #dummyuserassign

    SET @cmd = 'USE [' + @DBN + ']; INSERT INTO #dummyuserassign select DISTINCT

    CASE members.type_desc

    WHEN ''WINDOWS_USER''

    THEN ''CREATE USER [''+ members.name + ''] FOR LOGIN [''+ members.name+ '']'' + '' WITH DEFAULT_SCHEMA=['' + members.default_schema_name + '']''

    WHEN ''SQL_USER''

    THEN ''CREATE USER [''+ members.name + ''] FOR LOGIN [''+ members.name+ '']'' + '' WITH DEFAULT_SCHEMA=['' + members.default_schema_name + '']''

    END AS CreateUser

    from sys.database_principals members

    inner join sys.database_role_members drm

    on members.principal_id = drm.member_principal_id

    inner join sys.database_principals roles

    on drm.role_principal_id = roles.principal_id

    where members.name <> ''dbo''

    ORDER BY CreateUser'

    --PRINT @cmd

    EXEC (@cmd)

    if exists( SELECT * FROM #dummyuserassign)

    BEGIN

    PRINT 'USE ' + @DBN

    PRINT 'GO'

    PRINT ' '

    END

    DECLARE myCursorVariable3 CURSOR FOR

    SELECT LineVal FROM #dummyuserassign ORDER BY RecID

    OPEN myCursorVariable3

    -- Loop through all the files for the database

    FETCH NEXT FROM myCursorVariable3 INTO @lineval

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @lineval

    FETCH NEXT FROM myCursorVariable3 INTO @lineval

    END

    CLOSE myCursorVariable3

    DEALLOCATE myCursorVariable3

    END

    if @includeroleinfo = 1

    BEGIN

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

    /* Role Information */

    SELECT @collationname = collation_name FROM master.sys.databases WHERE name = @DBN

    if @collationname IS NULL

    print 'null for ' + @DBN

    if @collationname IS NULL

    SET @collationname = (SELECT collation_name FROM master.sys.databases WHERE name = 'master')

    SET @cmd = 'ALTER TABLE #dummyrolelist ALTER COLUMN RoleName varchar(200) COLLATE ' + @collationname + ' NULL'

    EXEC (@cmd)

    SET @cmd = 'ALTER TABLE #dummyDBRoles ALTER COLUMN RoleName varchar(200) COLLATE ' + @collationname + ' NULL'

    EXEC (@cmd)

    SET @cmd = 'ALTER TABLE #dummyDBRoles ALTER COLUMN UserName varchar(200) COLLATE ' + @collationname + ' NULL'

    EXEC (@cmd)

    SET @cmd = 'ALTER TABLE #dummyDBRoles ALTER COLUMN UserType varchar(200) COLLATE ' + @collationname + ' NULL'

    EXEC (@cmd)

    --Start with a clean table to load the values

    TRUNCATE TABLE #dummyDBRoles

    -- Get roles for this database and load into the temp table

    SET @cmd = 'USE [' + @DBN + ']; INSERT INTO #dummyDBRoles select CONVERT(varchar(200),roles.name) AS RoleName, CONVERT(varchar(200),members.name) AS UserName, CONVERT(varchar(200),members.type_desc) AS UserType from sys.database_principals members inner join sys.database_role_members drm on members.principal_id = drm.member_principal_id inner join sys.database_principals roles on drm.role_principal_id = roles.principal_id where members.name <> ''dbo'' ORDER BY members.name, roles.name'

    --PRINT @cmd

    EXEC (@cmd)

    -- Now add in any roles that are present in the database that do not have anyone assigned to them (those that are already in the temp table)

    SET @cmd = 'USE [' + @DBN + ']; INSERT INTO #dummyDBRoles SELECT CONVERT(varchar(200),name) AS RoleName, ''--none--'' As UserName, '''' AS UserType FROM sys.database_principals WHERE type = ''R'' and is_fixed_role = 0 and name <> ''public'' AND (name NOT IN (SELECT RoleName FROM #dummyDBRoles))'

    --PRINT @cmd

    EXEC (@cmd)

    -- now get a list of database roles that were created and print them as CREATE ROLE statements

    if @outputtype = 2

    BEGIN

    TRUNCATE TABLE #dummyrolelist

    SET @cmd = 'USE [' + @DBN + ']; INSERT INTO #dummyrolelist SELECT name FROM sys.database_principals WHERE type = ''R'' AND name <> ''public'' AND is_fixed_role = 0'

    --PRINT @cmd

    EXEC (@cmd)

    PRINT 'USE ' + @DBN

    PRINT 'GO'

    PRINT ' '

    DECLARE myCursorVariable4 CURSOR FOR

    SELECT RoleName FROM #dummyrolelist

    OPEN myCursorVariable4

    -- Loop through all the files for the database

    FETCH NEXT FROM myCursorVariable4 INTO @rn

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cmd = 'CREATE ROLE ' + @rn

    PRINT @cmd

    FETCH NEXT FROM myCursorVariable4 INTO @rn

    END

    CLOSE myCursorVariable4

    DEALLOCATE myCursorVariable4

    END

    --Get the length of the longest occurance of the columns

    SELECT @rnmax = ISNULL(MAX(len(RoleName)),0) FROM #dummyDBRoles

    SELECT @unmax = ISNULL(MAX(len(UserName)),0) FROM #dummyDBRoles

    SELECT @utmax = ISNULL(MAX(len(UserType)),0) FROM #dummyDBRoles

    --Set some minimum values so column doesn't print short

    if @rnmax < 25 SET @rnmax = 25

    if @unmax < 25 SET @unmax = 25

    if @utmax < 25 SET @utmax = 25

    --Set and print the column headings for the role information

    SET @col1nm = 'RoleName'

    SET @col1len = @rnmax

    SET @col2nm = ''

    SET @col2len = 5

    SET @col3nm = 'UserName'

    SET @col3len = @unmax

    SET @col4nm = ''

    SET @col4len = 5

    SET @col5nm = 'UserType'

    SET @col5len = @utmax

    PRINT ' '

    PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm))

    PRINT SPACE(10) + REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len) + SPACE(@col4len) + REPLICATE('=',@col5len)

    -- Print the script to set the database context

    if @outputtype = 2

    BEGIN

    PRINT 'USE ' + @DBN

    PRINT 'GO'

    PRINT ' '

    END

    --statement to get all roles for this database

    --SELECT name FROM sys.database_principals WHERE type = 'R' and is_fixed_role = 0 and name <> 'public'

    --can use to script the CREATE ROLE statements

    -- Now loop through the roles

    DECLARE backupFiles2 CURSOR FOR

    SELECT RoleName, UserName, UserType FROM #dummyDBRoles ORDER BY RoleName

    OPEN backupFiles2

    -- Loop through all the files for the database

    FETCH NEXT FROM backupFiles2 INTO @rn, @un, @ut

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --Set and print the row details for the role information

    SET @col1nm = SUBSTRING(@rn,1,@rnmax)

    SET @col3nm = SUBSTRING(@un,1,@unmax)

    SET @col5nm = SUBSTRING(@ut,1,@utmax)

    if @outputtype = 1

    PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm))

    if @outputtype = 2

    BEGIN

    if @col3nm <> '--none--'

    PRINT 'exec sp_addrolemember [' + @col1nm + '], [' + @col3nm + '] --Usertype= ' + @col5nm

    else

    PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm))

    END

    FETCH NEXT FROM backupFiles2 INTO @rn, @un, @ut

    END

    CLOSE backupFiles2

    DEALLOCATE backupFiles2

    PRINT ' '

    PRINT ' '

    END

    if @includeobjlvlperms = 1

    BEGIN

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

    /* Object-Level Permissions Information */

    --Start with a clean table to load the values

    TRUNCATE TABLE #dummyDBPerms

    -- Get permissions for this database and load into the temp table

    -- I'm sure some of this part came from elsewhere. My appologies to the originator.

    SET @cmd = 'USE [' + @DBN + ']; INSERT INTO #dummyDBPerms '

    SET @cmd = @cmd + 'select p.state_desc, p.permission_name, s.name, o.name, u.name, CASE o.type WHEN ''P'' THEN ''SPROC''

    WHEN ''V'' THEN ''View''

    WHEN ''U'' THEN ''Table''

    WHEN ''FN'' THEN ''Function (scaler)''

    WHEN ''TF'' THEN ''Function (table-valued)''

    ELSE o.type_desc END AS ObjectType,

    CONVERT(varchar(200),u.type_desc) AS UserType

    from sys.database_permissions p

    inner join sys.objects o on p.major_id = o.object_id

    inner join sys.schemas s on s.schema_id = o.schema_id

    inner join sys.database_principals u on p.grantee_principal_id = u.principal_id

    ORDER BY o.type, o.name collate Latin1_general_CI_AS, u.name collate Latin1_general_CI_AS'

    --PRINT @cmd

    EXEC (@cmd)

    --Get the length of the longest occurance of each of the columns

    SELECT @sdmax = ISNULL(MAX(len(StateDesc)),0) FROM #dummyDBPerms

    SELECT @pnmax = ISNULL(MAX(len(PermName)),0) FROM #dummyDBPerms

    SELECT @snmax = ISNULL(MAX(len(SchemaName)),0) FROM #dummyDBPerms

    SELECT @onmax = ISNULL(MAX(len(ObjectName)),0) FROM #dummyDBPerms

    SELECT @unmax = ISNULL(MAX(len(UserName)),0) FROM #dummyDBPerms

    SELECT @pdmax = ISNULL(MAX(len(ObjectType)),0) FROM #dummyDBPerms

    SELECT @utmax = ISNULL(MAX(len(UserType)),0) FROM #dummyDBPerms

    --Set some minimum values so column doesn't print short

    if @sdmax < 15 SET @sdmax = 15

    if @pnmax < 15 SET @pnmax = 15

    if @snmax < 10 SET @snmax = 10

    if @onmax < 15 SET @onmax = 15

    if @unmax < 15 SET @unmax = 15

    if @pdmax < 15 SET @pdmax = 15--ObjectType

    if @utmax < 15 SET @utmax = 15--UserType

    --Set and print the column headings for the permissions information

    SET @col1nm = 'StateDesc'

    SET @col1len = @sdmax

    SET @col2nm = ''

    SET @col2len = 5

    SET @col3nm = 'PermName'

    SET @col3len = @pnmax

    SET @col4nm = ''

    SET @col4len = 5

    SET @col5nm = 'Schema'

    SET @col5len = @snmax

    SET @col6nm = ''

    SET @col6len = 5

    SET @col7nm = 'Object'

    SET @col7len = @onmax

    SET @col8nm = ''

    SET @col8len = 5

    SET @col9nm = 'User'

    SET @col9len = @unmax

    SET @col10nm = ''

    SET @col10len = 5

    SET @col11nm = 'ObjectType'

    SET @col11len = @pdmax

    SET @col12nm = ''

    SET @col12len = 5

    SET @col13nm = 'UserType'

    SET @col13len = @utmax

    PRINT ' '

    PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len)+ @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len)+ @col7nm + SPACE(@col7len-len(@col7nm)) + SPACE(@col8len) + @col9nm + SPACE(@col9len-len(@col9nm)) + SPACE(@col10len) + @col11nm + SPACE(@col11len-len(@col11nm)) + SPACE(@col12len) + @col13nm + SPACE(@col13len-len(@col13nm))

    PRINT SPACE(10) + REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len) + SPACE(@col4len) + REPLICATE('=',@col5len) + SPACE(@col6len) + REPLICATE('=',@col7len) + SPACE(@col8len) + REPLICATE('=',@col9len) + SPACE(@col10len) + REPLICATE('=',@col11len) + SPACE(@col12len) + REPLICATE('=',@col13len)

    --Loop through the permissions for this database and format and print them

    DECLARE backupFiles2 CURSOR FOR

    SELECT StateDesc,PermName,SchemaName,ObjectName,UserName,ObjectType,UserType FROM #dummyDBPerms ORDER BY Schemaname,ObjectName,UserName

    OPEN backupFiles2

    -- Loop through all the files for the database

    FETCH NEXT FROM backupFiles2 INTO @sd, @pn, @SN, @on, @un, @pd, @ut

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --Set and print the row details for the permissions information

    SET @col1nm = SUBSTRING(@sd,1,@sdmax)

    SET @col3nm = SUBSTRING(@pn,1,@pnmax)

    SET @col5nm = SUBSTRING(@sn,1,@snmax)

    SET @col7nm = SUBSTRING(@on,1,@onmax)

    SET @col9nm = SUBSTRING(@un,1,@unmax)

    SET @col11nm = SUBSTRING(@pd,1,@pdmax)

    SET @col13nm = SUBSTRING(@ut,1,@utmax)

    --print the detail record for the permissions

    if @outputtype = 1

    PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len)+ @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len)+ @col7nm + SPACE(@col7len-len(@col7nm)) + SPACE(@col8len) + @col9nm + SPACE(@col9len-len(@col9nm)) + SPACE(@col10len) + @col11nm + SPACE(@col11len-len(@col11nm)) + SPACE(@col12len) + @col13nm + SPACE(@col13len-len(@col13nm))

    if @outputtype = 2

    PRINT @col1nm + ' ' + @col3nm + ' ON [' + @col5nm + '].[' + @col7nm + '] TO [' + @col9nm + '] --ObjectType=' + @col11nm + ' UserType=' + @col13nm

    FETCH NEXT FROM backupFiles2 INTO @sd, @pn, @SN, @on, @un, @pd,@ut

    END

    CLOSE backupFiles2

    DEALLOCATE backupFiles2

    PRINT ' '

    PRINT ' '

    END

    if @outputtype = 2 AND @includedefaultdb = 1

    BEGIN

    if EXISTS (SELECT name FROM master.sys.server_Principals WHERE type in ('G','S','U') AND default_database_name = @DBN)

    BEGIN

    PRINT ' '

    PRINT ' '

    PRINT '-- Here are the logins and their default database settings'

    PRINT ' '

    DECLARE myCursorVariable CURSOR FOR

    SELECT name, default_database_name as DefaultDB

    FROM master.sys.server_Principals

    WHERE type in ('G','S','U') AND default_database_name = @DBN

    ORDER BY name

    OPEN myCursorVariable

    -- Loop through all the files for the database

    FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'ALTER LOGIN [' + @loginname + '] WITH DEFAULT_DATABASE = ' + @dbnametouse

    FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse

    END

    CLOSE myCursorVariable

    DEALLOCATE myCursorVariable

    PRINT ' '

    PRINT ' '

    END

    END

    PRINT '==========================================================================================================='

    --Get the next database name and info to use in the database loop

    FETCH NEXT FROM backupFiles INTO @DBN, @rm, @cl, @rwd

    END

    CLOSE backupFiles

    DEALLOCATE backupFiles

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

    --Dispose of the temporary tables

    DROP TABLE #DummyDBDesc

    DROP TABLE #dummyDBRoles

    DROP TABLE #dummyDBUsers

    DROP TABLE #dummyDBPerms

    DROP TABLE #dummyuserassign

    DROP TABLE #dummyrolelist

    SET NOCOUNT OFF

  • Thanks, this is excellent

  • Thank you all!

    What I need is only the logins needs to access to the database to be moved. I have no intention to move all logins, which are not related to the database to be moved.

    Any more idea?

    Many thanks once again.

    business plans[/url]

  • This will extract only users from selected database. Got it here at sqlservercentral, can't remember who though.

    USE DATABASE

    GO

    select'CREATE LOGIN ' + sl.name +

    ' WITH PASSWORD = ' + sys.fn_varbintohexstr(sl.password_hash) +

    ' HASHED, SID = ' + sys.fn_varbintohexstr(sl.sid) +

    ', DEFAULT_DATABASE = ' + quotename(sl.default_database_name) +

    ', DEFAULT_LANGUAGE = ' + sl.default_language_name +

    ', CHECK_EXPIRATION = ' +

    case

    when sl.is_expiration_checked = 0 then 'off'

    else 'on'

    end + ', CHECK_POLICY = ' +

    case

    when sl.is_policy_checked = 0 then 'off'

    else 'on'

    end

    from sys.sql_logins sl

    where exists (select sid from sys.database_principals dp

    where dp.sid = sl.sid) and sl.principal_id > 4

    UNION ALL

    select'CREATE LOGIN ' + QUOTENAME(sp.name) +

    ' FROM WINDOWS WITH DEFAULT_DATABASE = ' +

    quotename(sp.default_database_name) + ', DEFAULT_LANGUAGE = ' +

    sp.default_language_name

    from sys.server_principals sp

    where exists (select sid from sys.database_principals dp

    where dp.sid = sp.sid) AND sp.principal_id > 4 AND sp.type IN ('G','U')

  • Rade_ (2/21/2013)


    This will extract only users from selected database. Got it here at sqlservercentral, can't remember who though.

    USE DATABASE

    GO

    select'CREATE LOGIN ' + sl.name +

    ' WITH PASSWORD = ' + sys.fn_varbintohexstr(sl.password_hash) +

    ' HASHED, SID = ' + sys.fn_varbintohexstr(sl.sid) +

    ', DEFAULT_DATABASE = ' + quotename(sl.default_database_name) +

    ', DEFAULT_LANGUAGE = ' + sl.default_language_name +

    ', CHECK_EXPIRATION = ' +

    case

    when sl.is_expiration_checked = 0 then 'off'

    else 'on'

    end + ', CHECK_POLICY = ' +

    case

    when sl.is_policy_checked = 0 then 'off'

    else 'on'

    end

    from sys.sql_logins sl

    where exists (select sid from sys.database_principals dp

    where dp.sid = sl.sid) and sl.principal_id > 4

    UNION ALL

    select'CREATE LOGIN ' + QUOTENAME(sp.name) +

    ' FROM WINDOWS WITH DEFAULT_DATABASE = ' +

    quotename(sp.default_database_name) + ', DEFAULT_LANGUAGE = ' +

    sp.default_language_name

    from sys.server_principals sp

    where exists (select sid from sys.database_principals dp

    where dp.sid = sp.sid) AND sp.principal_id > 4 AND sp.type IN ('G','U')

    This looks like mine 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Note that if you move the hashed_passwords over, you're using the old SQL 2005 (0x0100) type password hashes SHA1(Binary(UCS2(Password+Salt))), not the newer, very slightly better SQL 2012 (0x0200) type password hashes SHA512(Binary(UCS2(Password+Salt))).

    Additionally, if you put the plaintext password in, you'll get a different salt, and the hash will therefore be different between Instance A and Instance B (regardless of version). If you use the hashed_password, any attacker seeing the hashes knows that cracking the one also cracks the other.

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

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