Keeping roles and security in sync mirrored DB's

  • Hi

    What is best way to keep roles and security in sync on mirrored systems? (both Instance and DB logins)

    What do people have set-up to ensure that in the event of a disaster, users can log into the switched DB?

    Thanks

  • Since you can't mirror system databases which is where this info is stored - The best thing to do is to script out the logins and permissions. Do this on a regular basis.

    http://support.microsoft.com/kb/246133?wa=wsignin1.0

    And below, which I got from someone on this site

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

    /* = 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 @outputtype int

    DECLARE @prodlevel varchar(25)

    DECLARE @version varchar(250)

    DEClARE @prodver varchar(50)

    DECLARE @edition varchar(50)

    DECLARE @includeobjlvlperms bit

    DECLARE @includeroleinfo bit

    DECLARE @includedefaultdb bit

    DECLARE @usnm varchar(128)

    DECLARE @ustp varchar(60)

    DECLARE @stdsc varchar(60)

    DECLARE @permnm varchar(128)

    DECLARE @collationname varchar(200)

    DECLARE @lineval varchar(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 @lvaltouse varchar(2000)

    DECLARE @lvallength int

    DECLARE @lvalct int

    DECLARE @spotcat int

    DECLARE @spotcatval int

    DECLARE @lval1 varchar(2000)

    DECLARE @lval2 varchar(2000)

    DECLARE @lval3 varchar(2000)

    DECLARE @lval4 varchar(2000)

    DECLARE @lval5 varchar(2000)

    DECLARE @lval6 varchar(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,

    LineVal varchar(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

    ( StateDesc varchar(200),

    PermName varchar(200),

    SchemaName varchar(200),

    ObjectName varchar(200),

    UserName varchar(200),

    ObjectType varchar(200),

    UserType varchar(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

    ( RecID int IDENTITY NOT NULL,

    ServerName varchar(128) NULL,

    DBName varchar(100) NULL,

    RecoveryModel varchar(10) NULL,

    CompatibilityLevel varchar(30) NULL,

    ReadWriteDesc varchar(10) NULL

    )

    CREATE TABLE #dummyDBRoles

    ( RoleName varchar(200),

    UserName varchar(200),

    UserType varchar(200)

    )

    CREATE TABLE #dummyrolelist

    ( RoleName varchar(200)

    )

    CREATE TABLE #dummyDBUsers

    ( UserName varchar(200),

    UserType varchar(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

  • SQLSACT (6/12/2013)


    Since you can't mirror system databases which is where this info is stored - The best thing to do is to script out the logins and permissions. Do this on a regular basis.

    http://support.microsoft.com/kb/246133?wa=wsignin1.0

    And below, which I got from someone on this site

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

    /* = 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 @outputtype int

    DECLARE @prodlevel varchar(25)

    DECLARE @version varchar(250)

    DEClARE @prodver varchar(50)

    DECLARE @edition varchar(50)

    DECLARE @includeobjlvlperms bit

    DECLARE @includeroleinfo bit

    DECLARE @includedefaultdb bit

    DECLARE @usnm varchar(128)

    DECLARE @ustp varchar(60)

    DECLARE @stdsc varchar(60)

    DECLARE @permnm varchar(128)

    DECLARE @collationname varchar(200)

    DECLARE @lineval varchar(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 @lvaltouse varchar(2000)

    DECLARE @lvallength int

    DECLARE @lvalct int

    DECLARE @spotcat int

    DECLARE @spotcatval int

    DECLARE @lval1 varchar(2000)

    DECLARE @lval2 varchar(2000)

    DECLARE @lval3 varchar(2000)

    DECLARE @lval4 varchar(2000)

    DECLARE @lval5 varchar(2000)

    DECLARE @lval6 varchar(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,

    LineVal varchar(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

    ( StateDesc varchar(200),

    PermName varchar(200),

    SchemaName varchar(200),

    ObjectName varchar(200),

    UserName varchar(200),

    ObjectType varchar(200),

    UserType varchar(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

    ( RecID int IDENTITY NOT NULL,

    ServerName varchar(128) NULL,

    DBName varchar(100) NULL,

    RecoveryModel varchar(10) NULL,

    CompatibilityLevel varchar(30) NULL,

    ReadWriteDesc varchar(10) NULL

    )

    CREATE TABLE #dummyDBRoles

    ( RoleName varchar(200),

    UserName varchar(200),

    UserType varchar(200)

    )

    CREATE TABLE #dummyrolelist

    ( RoleName varchar(200)

    )

    CREATE TABLE #dummyDBUsers

    ( UserName varchar(200),

    UserType varchar(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

    Thank you, will check this method out.

  • I wouldn't worry too much about the DB logins and their permissions as these are stored in the user database itself.

    The main thing is the instance level logins and SID's - These are kep in the master database

Viewing 4 posts - 1 through 3 (of 3 total)

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