Script Login with Permissions to User Databases

  • I need to Script a Login's Permissions to include permissions to each User Database.

    In this case I only need it for one Login that is used as a remote login for a Linked Server.

    Any help would be greatly appreciated.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • this was my first google hit for "Script Login with Permissions to User Databases":

    looks like exactly what you want, right?

    http://www.sql-server-performance.com/2002/object-permission-scripts/

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • It's nice but it converts ' to ‘ when you copy & paste.

    I tried replacing but I still had errors.

    Thanks for the link.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • You have to run the script for each database.

    Also I get an error when running the script.

    Msg 911, Level 16, State 1, Line 33

    Could not locate entry in sysdatabases for database '' + DB_NAME() + ''. No entry found with that name. Make sure that the name is entered correctly.

    I need something that script out a Login and it's their fixed database roles for all Databases on a SQL Server Instance.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I have posted this previously to this website, but I've made a few enhancements to it, so I'll post it again. This is my script to run a Security Audit for an instance. Runs for all databases, unless you put a specific database in the WHERE clause of the appropriate SELECT.

    It creates either columnar output, or actual assignment statements. Set the @outputtype variable to whichever is needed.

    In that same area, you can set whether to include the object level permissions, and the role information.

    It's not the cleanest or the fastest, but it does a really good job for what I needed.

    Uses print, so go to the messages tab in the output in Management Studio. Can run as output to file so prints directly to a file, especially useful if a lot of databases, or a lot of permissions.

    Here is the script. Hope it helps.

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

    /* Instance Security Audit Documentation */

    /* Runs for all databases, unless a specific database is used in the WHERE clause below */

    /* */

    /* By VikingDBA 12/28/2011 */

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

    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 @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

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

    SET @includeobjlvlperms = 1

    SET @includeroleinfo = 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

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

    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 ' 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 #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) + ' - Denali'

    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 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 @includeroleinfo = 1

    BEGIN

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

    /* Role Information */

    --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 SQL_Latin1_General_CP1_CI_AS WHERE type = ''R'' and is_fixed_role = 0 and name <> ''public'' AND (name NOT IN (SELECT RoleName FROM #dummyDBRoles))'

    --PRINT @cmd

    EXEC (@cmd)

    --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

    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

    SET NOCOUNT OFF

  • The script is awesome, but do you have a version that support SQL 2000? Yes, some of us poor souls are STILL on 2000.

    Thanks!

  • No, I don't have a version for SQL 2000 at this point. I'll see what I can do.

  • Alright. Here is a SQL 2000 version of the Instance Security Audit Documentation script. Just finished it, so if you find any errors or issues, let me know. Same basic structure, just changed the SELECT statements.

    Same caveats; Not pretty, not fast, but it gets the job done.

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

    /* Instance Security Audit Documentation FOR SQL Server 2000 */

    /* Runs for all databases, unless a specific database is used in the @dbname variable below */

    /* */

    /* By VikingDBA 07/24/2012 */

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

    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 @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 @dbnamevarchar(128)

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

    SET @includeroleinfo = 1

    SET @includeobjlvlperms = 1

    SET @dbname = NULL-- set to other than null for specific database

    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

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

    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 ' 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),

    isWinGrpbit,

    isWinUserbit,

    isSQLServUserbit,

    isSQLServRolebit,

    isSQLServAppRolebit

    )

    CREATE TABLE #dummyDBUsers

    (UserNamevarchar(200),

    UserTypevarchar(200),

    isWinGrpbit,

    isWinUserbit,

    isSQLServUserbit,

    isSQLServRolebit,

    isSQLServAppRolebit

    )

    INSERT INTO #DummyDBDesc

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

    CASE cmptlevel

    WHEN 70 THEN CONVERT(varchar(4),cmptlevel) + ' - SQL 7'

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

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

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

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

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

    ELSE CONVERT(varchar(4),cmptlevel)

    END AS CompatibilityLevel,

    CONVERT(varchar(10),'') as ReadWriteDesc

    FROM dbo.sysdatabases

    WHERE name NOT IN('tempdb','master','msdb','model') and name NOT LIKE '%ReportServer%' AND (@dbname is null or name = @dbname)

    ORDER BY name

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

    DECLARE @dbdesc2 varchar(4000)

    DECLARE backupFiles8 CURSOR FOR

    SELECT DBName FROM #DummyDBDesc ORDER BY DBName

    OPEN backupFiles8

    DECLARE @dbn2 varchar(128)

    -- Loop through all the files for the database

    FETCH NEXT FROM backupFiles8 INTO @dbn2

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @dbdesc2 = convert(sysname,DatabasePropertyEx(@dbn2,'Status'))--Online, offline, etc.

    --Do not do anything with this at this point. May want it later

    SET @dbdesc2 = convert(sysname,DatabasePropertyEx(@dbn2,'Updateability'))--Recovery Mode (Read_Write, Read_Only, etc.)

    UPDATE #DummyDBDesc SET ReadWriteDesc = @dbdesc2 WHERE DBName = @dbn2

    SET @dbdesc2 = convert(sysname,DatabasePropertyEx(@dbn2,'UserAccess'))--Multi_User, Restricted_User, etc.

    --Do not do anything with this at this point. May want it later

    SET @dbdesc2 = convert(sysname,DatabasePropertyEx(@dbn2,'Recovery'))--Recovery Mode

    UPDATE #DummyDBDesc SET RecoveryModel = @dbdesc2 WHERE DBName = @dbn2

    --NOTE: See code for dbo.sp_helpdb extended stored proc for more info and more properties

    FETCH NEXT FROM backupFiles8 INTO @dbn2

    END

    CLOSE backupFiles8

    DEALLOCATE backupFiles8

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

    DECLARE backupFiles CURSOR FOR

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

    OPEN backupFiles

    DECLARE @DBN varchar(128)

    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),'''') as UserType, isntgroup AS isWinGrp, isntuser AS isWinUser, issqluser AS isSQLServUser, issqlrole AS isSQLServRole, isapprole AS isSQLServAppRole FROM dbo.sysusers WHERE islogin = 1 AND (name NOT IN (''guest'',''dbo'',''system_function_schema'',''INFORMATION_SCHEMA'',''sys''))'

    --PRINT @cmd

    EXEC (@cmd)

    -- make updates to set the user type according to the flags

    UPDATE #dummyDBUsers SET UserType = 'SQL_USER' WHERE isSQLServUser = 1

    UPDATE #dummyDBUsers SET UserType = 'SQL_ROLE' WHERE isSQLServRole = 1

    UPDATE #dummyDBUsers SET UserType = 'WIN_USER' WHERE isWinUser = 1

    UPDATE #dummyDBUsers SET UserType = 'WIN_GROUP' WHERE isWinGrp = 1

    --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 @includeroleinfo = 1

    BEGIN

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

    /* Role Information */

    --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),'''') AS UserType, members.isntgroup AS isWinGrp, members.isntuser AS isWinUser, members.issqluser AS isSQLServUser, members.issqlrole AS isSQLServRole, members.isapprole AS isSQLServAppRole from dbo.sysusers members inner join dbo.sysmembers drm on members.[uid] = drm.[memberuid] inner join dbo.sysusers roles on drm.[groupuid] = roles.[uid] 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, isntgroup AS isWinGrp, isntuser AS isWinUser, issqluser AS isSQLServUser, issqlrole AS isSQLServRole, isapprole AS isSQLServAppRole FROM dbo.sysusers SQL_Latin1_General_CP1_CI_AS WHERE issqlrole=1 AND altuid = 0 and name <> ''public'' AND (name NOT IN (SELECT RoleName FROM #dummyDBRoles))'

    --PRINT @cmd

    EXEC (@cmd)

    UPDATE #dummyDBRoles SET UserType = 'SQL_USER' WHERE isSQLServUser = 1

    UPDATE #dummyDBRoles SET UserType = 'SQL_ROLE' WHERE isSQLServRole = 1

    UPDATE #dummyDBRoles SET UserType = 'WIN_USER' WHERE isWinUser = 1

    UPDATE #dummyDBRoles SET UserType = 'WIN_GROUP' WHERE isWinGrp = 1

    --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

    -- 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

    -- The codes for the following came from Angel Huerta, 2006/01/02 in http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31614/

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

    SET @cmd = @cmd + 'select

    CASE pr.protecttype WHEN 204 THEN ''GRANT_W_GRANT''

    WHEN 205 THEN ''GRANT''

    WHEN 206 THEN ''REVOKE''

    ELSE ''UNKNOWN''

    END AS StateDesc,

    CASE pr.action WHEN 26 THEN ''REFERENCES''

    WHEN 178 THEN ''CREATE FUNCTION''

    WHEN 193 THEN ''SELECT''

    WHEN 195 THEN ''INSERT''

    WHEN 196 THEN ''DELETE''

    WHEN 197 THEN ''UPDATE''

    WHEN 198 THEN ''CREATE TABLE''

    WHEN 203 THEN ''CREATE DATABASE''

    WHEN 207 THEN ''CREATE VIEW''

    WHEN 222 THEN ''CREATE PROCEDURE''

    WHEN 224 THEN ''EXECUTE''

    WHEN 228 THEN ''BACKUP DATABASE''

    WHEN 233 THEN ''CREATE DEFAULT''

    WHEN 235 THEN ''BACKUP LOG''

    WHEN 236 THEN ''CREATE RULE''

    ELSE ''UNKNOWN''

    END AS [PermName],

    CONVERT(varchar(200),'''') AS SchemaName,

    o.name as ''ObjectName'',

    u.name as ''UserName'',

    CASE o.xtype

    WHEN ''FN'' THEN ''Scalar function''

    WHEN ''IF'' THEN ''Inlined table-function''

    WHEN ''P'' THEN ''Stored procedure''

    WHEN ''TF'' THEN ''Table function''

    WHEN ''TR'' THEN ''Trigger''

    WHEN ''U'' THEN ''User table''

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

    WHEN ''X'' THEN ''Extended Stored Proc''

    WHEN ''S'' THEN ''System Table''

    ELSE ''UNKNOWN''

    END AS [ObjectType],

    CONVERT(varchar(200),'''') AS UserType

    FROM dbo.sysprotects pr

    JOIN dbo.sysobjects o ON pr.id = o.id

    JOIN dbo.sysusers u ON pr.uid = u.uid

    ORDER BY o.name, u.name, o.xtype'

    --PRINT @cmd

    EXEC (@cmd)

    UPDATE #dummyDBPerms SET UserType = ISNULL((SELECT #dummyDBUsers.UserType FROM #dummyDBUsers WHERE #dummyDBUsers.UserName = #dummyDBPerms.UserName),'')

    --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 [' + @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

    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

    SET NOCOUNT OFF

  • Thank you!

  • Hi

    This is fabulous. Thank you very much for making it available to us.

    I am having a couple of problems with this script. Please excuse any obvious newbie errors (but point them out to me so I learn) as I am still a relatively new DBA (just over 8 months now) and as all of my team mates have left and not been replaced yet, right now I am the only one and have no peer support on-site.

    When I try to run the script on servers with off-line databases it stops at the first off-line database. My T-SQL skills are not good enough to resolve this I don't think.

    Do I change line 283

    FROM sys.databases

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

    by adding

    AND state_desc = ONLINE

    Do I need to change anything else elsewhere?

    Secondly, I am getting the error

    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.

    This is coming up on servers which have Sharepoint databases on them which have to have their Collation set to Latin1_General_CI_AS_KS_WS according to the Sharepoint team.

    I have googled this and as I understand it there is a need to add COLLATE Database default or COLLATE Latin1_General_CI_AS but I have no idea where

    Apologies if this is obvious to some.

    https://blog.robsewell.com Its where I blog
    SQL Community Slack Channel https://sqlps.io/slack
    The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
    Data South West User Group http://sqlsouthwest.co.uk/[/url]

  • compare the different collation data bases using

    string1 = string2 collate [string1 colaltion]

    Regards
    Durai Nagarajan

  • While it works for my situation, it may need tweeking to work in someone else's environment. I never claimed it was professional :). I made it available for others to use and modify according to their situation. I don't have any off-line databases, so that didn't even enter in to the testing. I only have one SQL 2000 box still going, and I'm not even responsible for it, but have access to it, so I used it for testing.

    Please modify the script as you see fit to match your environment. My scripts are always a work in progress.......

  • mrrobsewell (8/3/2012)


    Hi

    Secondly, I am getting the error

    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.

    Hi all, I change the dynamic query to default_databse to avoid this problem, near line 450 :hehe:

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

    /* Instance Security Audit Documentation */

    /* Runs for all databases, unless a specific database is used in the WHERE clause below */

    /* */

    /* By VikingDBA 12/28/2011 */

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

    /* Modifications:

    20121116 DAF Little change to work with DBs with different collates.

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

    */

    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 @usnmvarchar(128)

    DECLARE @ustpvarchar(60)

    DECLARE @stdscvarchar(60)

    DECLARE @permnm varchar(128)

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

    SET @includeobjlvlperms = 1

    SET @includeroleinfo = 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

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

    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

    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)

    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

    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))

    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 intIDENTITYNOT NULL,

    ServerName varchar(128)NULL,

    DBName varchar(100)NULL,

    RecoveryModel varchar(10) NULL,

    CompatibilityLevelvarchar(30) NULL,

    ReadWriteDesc varchar(10) NULL

    )

    CREATE TABLE #dummyDBRoles

    (RoleNamevarchar(200),

    UserNamevarchar(200),

    UserTypevarchar(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 name = 'MyDatabase'

    ORDER BY name

    DECLARE backupFiles CURSOR FOR

    SELECT DBName, RecoveryModel, CompatibilityLevel, ReadWriteDesc FROM #DummyDBDesc

    where DBName = 'SMG_Siniestros'

    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 @includeroleinfo = 1

    BEGIN

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

    /* Role Information */

    --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 database_default WHERE type = ''R'' and is_fixed_role = 0 and name <> ''public'' AND (name NOT IN (SELECT RoleName collate database_default FROM #dummyDBRoles))'

    --PRINT @cmd

    EXEC (@cmd)

    --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

    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

    SET NOCOUNT OFF

Viewing 13 posts - 1 through 12 (of 12 total)

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