No idea if this is what you are talking about, but hope it helps someone.
I have posted this previously to several forum topics. Been busy for past year, so haven't posted since then. Made some changes to it.
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 (report style), 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, the role information, and whether to include their default db information.
It also prints the users that are principals but are not in any database, and prints users that are in databases that have no instance login associated (orphan).
It's not the cleanest or the fastest, but it does a really good job for what I needed.
It prints server role security settings (who is a sysadmin, serveradmin, dbcreator, etc.)
It uses the PRINT command, 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.
/* ====================================================================================================================== */
/* = 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
02/12/2013 Changed to also print the users that are principals but are not in any databases (could be assigned
to a database in instance principal, but do not exist in any individual database security), and users
that are in databases that have no instance login associated.
04/15/2013 Note that if a specific database is used, then the things added on 02/12/2013 are incorrect because
the other instance databases are not included, so the messages are not necessarily correct.
09/30/2013 Fixed to create the CREATE USER statement for WINDOWS_GROUPS (was only doing for SQL_USER and
WINDOWS_USER).
01/08/2014 Changed to do higher level permissions (like schema-wide permissions)
Dependencies:
This script depends on the following to exist:
none
Summary:
This script creates documentation for all databases in an instance, including the server level permissions, database role
permissions, and individual database object permissions.
Note that changing @outputtype = 1 sets for columnar (report style) and value of 2 creates the assignment statements
NOTE: Runs for all databases, unless a specific database is used in the WHERE clause below.
Also, see User Settable Variables section below to see if any variables need to be set.
*/
Use MASTER
SET NOCOUNT ON
/* ==================================================================================================================== */
-- Security Audit for SERVER Roles
DECLARE @sr varchar(100)
DECLARE @mn varchar(150)
DECLARE @cmd varchar(4000)
DECLARE @col1nm varchar(200)
DECLARE @col2nm varchar(200)
DECLARE @col3nm varchar(200)
DECLARE @col4nm varchar(200)
DECLARE @col5nm varchar(200)
DECLARE @col6nm varchar(200)
DECLARE @col7nm varchar(200)
DECLARE @col8nm varchar(200)
DECLARE @col9nm varchar(200)
DECLARE @col10nm varchar(200)
DECLARE @col11nm varchar(200)
DECLARE @col12nm varchar(200)
DECLARE @col13nm varchar(200)
DECLARE @col14nm varchar(200)
DECLARE @col15nm varchar(200)
DECLARE @col16nm varchar(200)
DECLARE @col17nm varchar(200)
DECLARE @col18nm varchar(200)
DECLARE @col19nm varchar(200)
DECLARE @col20nm varchar(200)
DECLARE @col1len int
DECLARE @col2len int
DECLARE @col3len int
DECLARE @col4len int
DECLARE @col5len int
DECLARE @col6len int
DECLARE @col7len int
DECLARE @col8len int
DECLARE @col9len int
DECLARE @col10len int
DECLARE @col11len int
DECLARE @col12len int
DECLARE @col13len int
DECLARE @col14len int
DECLARE @col15len int
DECLARE @col16len int
DECLARE @col17len int
DECLARE @col18len int
DECLARE @col19len int
DECLARE @col20len int
DECLARE @col1max int
DECLARE @col2max int
DECLARE @col3max int
DECLARE @col4max int
DECLARE @col5max int
DECLARE @col6max int
DECLARE @col7max int
DECLARE @col8max int
DECLARE @col9max int
DECLARE @col10max int
DECLARE @col11max int
DECLARE @col12max int
DECLARE @col13max int
DECLARE @col14max int
DECLARE @col15max int
DECLARE @col16max int
DECLARE @col17max int
DECLARE @col18max int
DECLARE @col19max int
DECLARE @col20max int
DECLARE @col1min int
DECLARE @col2min int
DECLARE @col3min int
DECLARE @col4min int
DECLARE @col5min int
DECLARE @col6min int
DECLARE @col7min int
DECLARE @col8min int
DECLARE @col9min int
DECLARE @col10min int
DECLARE @col11min int
DECLARE @col12min int
DECLARE @col13min int
DECLARE @col14min int
DECLARE @col15min int
DECLARE @col16min int
DECLARE @col17min int
DECLARE @col18min int
DECLARE @col19min int
DECLARE @col20min int
DECLARE @rn varchar(200)
DECLARE @un varchar(200)
DECLARE @ut varchar(200)
DECLARE @sd varchar(200)
DECLARE @pn varchar(200)
DECLARE @SN varchar(200)
DECLARE @on varchar(200)
DECLARE @pd varchar(200)
DECLARE @sdmax int
DECLARE @pnmax int
DECLARE @snmax int
DECLARE @onmax int
DECLARE @pdmax int
DECLARE @unmax int
DECLARE @rnmax int
DECLARE @utmax int
DECLARE @outputtypeint
DECLARE @prodlevel varchar(25)
DECLARE @versionvarchar(250)
DEClARE @prodvervarchar(50)
DECLARE @editionvarchar(50)
DECLARE @includeobjlvlpermsbit
DECLARE @includeroleinfobit
DECLARE @includedefaultdbbit
DECLARE @usnmvarchar(128)
DECLARE @ustpvarchar(60)
DECLARE @stdscvarchar(60)
DECLARE @permnm varchar(128)
DECLARE @collationnamevarchar(200)
DECLARE @linevalvarchar(2000)
DECLARE @lineval2varchar(2000)
DECLARE @usernamevarchar(200)
DECLARE @usertypevarchar(200)
DECLARE @loginname varchar(100)
DECLARE @dbnametouse sysname
DECLARE @nmvarchar(200)
DECLARE @TDvarchar(200)
DECLARE @nmaltvarchar(200)
DECLARE @defdbvarchar(300)
DECLARE @maxnmint
/* ================================================================================================ */
-- User Settable Variables
SET @outputtype = 1-- 1=columnar 2=assignment statements
SET @includeobjlvlperms = 1
SET @includeroleinfo = 1
SET @includedefaultdb = 1
/* ================================================================================================ */
SELECT @prodlevel=CONVERT(varchar(25),SERVERPROPERTY('ProductLevel'))
SELECT @version=CONVERT(varchar(250),@@VERSION)
SELECT @prodver=CONVERT(varchar(50),SERVERPROPERTY('ProductVersion'))
SELECT @edition=CONVERT(varchar(50),SERVERPROPERTY('Edition'))
/* ============================================================================ */
--Find split out line
DECLARE @lvaltousevarchar(2000)
DECLARE @lvallengthint
DECLARE @lvalctint
DECLARE @spotcatint
DECLARE @spotcatvalint
DECLARE @lval1varchar(2000)
DECLARE @lval2varchar(2000)
DECLARE @lval3varchar(2000)
DECLARE @lval4varchar(2000)
DECLARE @lval5varchar(2000)
DECLARE @lval6varchar(2000)
SET @lvaltouse = @version
SET @lvallength = LEN(@lvaltouse)
SET @lvalct = 1
SET @spotcat = 1
SET @lval1 = ''
SET @lval2 = ''
SET @lval3 = ''
SET @lval4 = ''
SET @lval5 = ''
SET @lval6 = ''
WHILE @spotcat <= @lvallength
BEGIN
SET @spotcatval = ASCII(SUBSTRING(@lvaltouse,@spotcat,1))
if @spotcatval = 10-- value we are looking for
SET @lvalct = @lvalct + 1-- set to go to the next line and start building it
else-- add to current value line
BEGIN
if @spotcatval <> 9-- values we are wanting to exclude
BEGIN
if @lvalct = 1
SET @lval1 = @lval1 + CHAR(@spotcatval)
if @lvalct = 2
SET @lval2 = @lval2 + CHAR(@spotcatval)
if @lvalct = 3
SET @lval3 = @lval3 + CHAR(@spotcatval)
if @lvalct = 4
SET @lval4 = @lval4 + CHAR(@spotcatval)
if @lvalct = 5
SET @lval5 = @lval5 + CHAR(@spotcatval)
if @lvalct = 6
SET @lval6 = @lval6 + CHAR(@spotcatval)
END
END
SET @spotcat = @spotcat + 1
END
--PRINT 'Line to split=' + @lvaltouse
--PRINT 'line1 = ' + @lval1
--PRINT 'line2 = ' + @lval2
--PRINT 'line3 = ' + @lval3
--PRINT 'line4 = ' + @lval4
--PRINT 'line5 = ' + @lval5
--PRINT 'line6 = ' + @lval6
/* ============================================================================= */
CREATE TABLE #dummyuserassign
(RecID int IDENTITY,
LineValvarchar(2000),
LineVal2 varchar(2000),
UserNamevarchar(200),
UserTypevarchar(200)
)
PRINT '============================================================================================================='
PRINT ' Security Audit For Server Instance ' + CONVERT(varchar(128),@@servername)
if @outputtype = 2
PRINT ' Assignment Statements'
PRINT ' For ' + CONVERT(varchar(128),getdate(),101) + ' ' + CONVERT(varchar(128),getdate(),108)
PRINT '============================================================================================================='
PRINT 'SQL Server Version: ' + @lval1
PRINT ' ' + @lval4
PRINT '============================================================================================================='
PRINT 'NOTE: Make sure to get list of logins using the sp_help_revlogin stored procedure in the master database.'
PRINT '============================================================================================================='
PRINT ' Server Role Security Settings'
PRINT ' '
PRINT ' '
CREATE TABLE #rolememberdummy
(ServerRole varchar(100),
MemberName varchar(150),
MemberSID varchar(2000)
)
CREATE TABLE #dummyDBPerms
(StateDescvarchar(200),
PermNamevarchar(200),
SchemaNamevarchar(200),
ObjectNamevarchar(200),
UserNamevarchar(200),
ObjectTypevarchar(200),
UserTypevarchar(200)
)
-- Security Audit
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'sysadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'securityadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'serveradmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'dbcreator'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'diskadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'processadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'setupadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'bulkadmin'
SET @col1nm = 'Role'
SET @col1len = 20
SET @col2nm = ''
SET @col2len = 8
SET @col3nm = 'Member Name'
SET @col3len = 30
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm
PRINT REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len)
--SELECT CONVERT(varchar(30),ServerRole) as ServerRole, CONVERT(varchar(30),MemberName) AS MemberName FROM #rolememberdummy
DECLARE backupFiles CURSOR FOR
SELECT ServerRole, MemberName FROM #rolememberdummy
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @sr, @mn
WHILE @@FETCH_STATUS = 0
BEGIN
SET @col1nm = @sr
SET @col1len = 20
SET @col2nm = ''
SET @col2len = 8
SET @col3nm = @mn
SET @col3len = 30
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm
FETCH NEXT FROM backupFiles INTO @sr, @mn
END
CLOSE backupFiles
DEALLOCATE backupFiles
DROP TABLE #rolememberdummy
PRINT ' '
PRINT ' '
PRINT '==========================================================================================================='
PRINT ' Server Level Permissions'
PRINT ' '
PRINT ' '
CREATE TABLE #serverpermdummy
(UserName varchar(128),
UserType varchar(60),
StateDesc varchar(60),
PermName varchar(128)
)
INSERT INTO #serverpermdummy
SELECT l.name as UserName, l.type_desc AS UserType, p.state_desc AS StateDesc, p.permission_name AS PermName
FROM sys.server_permissions AS p
JOIN sys.server_principals AS l ON p.grantee_principal_id = l.principal_id
WHERE ((permission_name <> 'CONNECT SQL' AND permission_name <> 'CONNECT') OR p.state_desc = 'DENY') AND l.type_desc <> 'CERTIFICATE_MAPPED_LOGIN' AND l.name NOT LIKE '%##MS_%'
ORDER BY l.principal_id
--SELECT * FROM sys.server_principals
SET @col1nm = 'User Name'
SET @col1len = 20
SET @col2nm = ''
SET @col2len = 8
SET @col3nm = 'User Type'
SET @col3len = 20
SET @col4nm = ''
SET @col4len = 8
SET @col5nm = 'State Desc'
SET @col5len = 20
SET @col6nm = ''
SET @col6len = 8
SET @col7nm = 'Permission'
SET @col7len = 30
SET @col1min = LEN(@col1nm)
SET @col3min = LEN(@col3nm)
SET @col5min = LEN(@col5nm)
SET @col7min = LEN(@col7nm)
--Get the length of the longest occurance of the columns
SELECT @col1max = ISNULL(MAX(len(LTRIM(RTRIM(UserName)))),0) FROM #serverpermdummy
SELECT @col3max = ISNULL(MAX(len(LTRIM(RTRIM(UserType)))),0) FROM #serverpermdummy
SELECT @col5max = ISNULL(MAX(len(LTRIM(RTRIM(StateDesc)))),0) FROM #serverpermdummy
SELECT @col7max = ISNULL(MAX(len(LTRIM(RTRIM(PermName)))),0) FROM #serverpermdummy
--Set some minimum values so column doesn't print short
if @col1max < @col1min SET @col1len = @col1min else SET @col1len = @col1max
if @col3max < @col3min SET @col3len = @col3min else SET @col3len = @col3max
if @col5max < @col5min SET @col5len = @col5min else SET @col5len = @col5max
if @col7max < @col7min SET @col7len = @col7min else SET @col7len = @col7max
if @outputtype = 1
BEGIN
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len) + @col7nm + SPACE(@col7len-len(@col7nm))
PRINT REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len) + SPACE(@col4len) + REPLICATE('=',@col5len) + SPACE(@col6len) + REPLICATE('=',@col7len)
END
else
if EXISTS (SELECT ISNULL(UserName,'') AS UserName, ISNULL(UserType,'') AS UserType, ISNULL(StateDesc,'') AS StateDesc, ISNULL(PermName,'') AS PermName FROM #serverpermdummy)
PRINT 'USE master;'
DECLARE backupFiles CURSOR FOR
SELECT ISNULL(UserName,'') AS UserName, ISNULL(UserType,'') AS UserType, ISNULL(StateDesc,'') AS StateDesc, ISNULL(PermName,'') AS PermName FROM #serverpermdummy
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @usnm, @ustp, @stdsc, @permnm
WHILE @@FETCH_STATUS = 0
BEGIN
SET @col1nm = @usnm
SET @col2nm = ''
SET @col3nm = @ustp
SET @col4nm = ''
SET @col5nm = @stdsc
SET @col6nm = ''
SET @col7nm = @permnm
if @outputtype = 1
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len) + @col7nm + SPACE(@col7len-len(@col7nm))
else
PRINT @stdsc + ' ' + @permnm + ' TO ' + @usnm + ';'
FETCH NEXT FROM backupFiles INTO @usnm, @ustp, @stdsc, @permnm
END
CLOSE backupFiles
DEALLOCATE backupFiles
DROP TABLE #serverpermdummy
PRINT ' '
PRINT ' '
PRINT '==========================================================================================================='
PRINT ' Information By Database'
PRINT ' '
PRINT ' '
CREATE TABLE #DummyDBDesc
(RecIDintIDENTITYNOT NULL,
ServerNamevarchar(128)NULL,
DBNamevarchar(100)NULL,
RecoveryModelvarchar(10)NULL,
CompatibilityLevelvarchar(30)NULL,
ReadWriteDescvarchar(10)NULL,
OwnerNamevarchar(100)NULL
)
CREATE TABLE #dummyDBRoles
(RoleNamevarchar(200),
UserNamevarchar(200),
UserTypevarchar(200)
)
CREATE TABLE #dummyrolelist
(RoleNamevarchar(200)
)
CREATE TABLE #dummyAllDBUsers
(UserNamevarchar(200),
UserTypevarchar(200),
DBNamevarchar(300)
)
CREATE TABLE #dummyDBUsers
(UserNamevarchar(200),
UserTypevarchar(200)
)
INSERT INTO #DummyDBDesc
select CONVERT(varchar(128),@@servername) AS ServerName, CONVERT(varchar(100),d.name) as DBName, CONVERT(varchar(10),d.recovery_model_desc) as RecoveryModel,
CASE d.compatibility_level
WHEN 80 THEN CONVERT(varchar(4),d.compatibility_level) + ' - SQL 2000 *'
WHEN 90 THEN CONVERT(varchar(4),d.compatibility_level) + ' - SQL 2005'
WHEN 100 THEN CONVERT(varchar(4),d.compatibility_level) + ' - SQL 2008'
WHEN 105 THEN CONVERT(varchar(4),d.compatibility_level) + ' - SQL 2008 R2'
WHEN 110 THEN CONVERT(varchar(4),d.compatibility_level) + ' - SQL 2012'
ELSE CONVERT(varchar(4),d.compatibility_level)
END AS CompatibilityLevel,
CASE d.is_read_only
WHEN 0 THEN CONVERT(varchar(10),'RW')
ELSE CONVERT(varchar(10),'R')
END as ReadWriteDesc,
sp.name as OwnerName
FROM sys.databases d
JOIN master.sys.server_principals sp ON d.owner_sid = sp.sid
WHERE d.name NOT IN('tempdb','master','msdb','model') and d.name NOT LIKE '%ReportServer%' AND d.state = 0
--AND name IN ('')
ORDER BY d.name
DECLARE backupFiles CURSOR FOR
SELECT DBName, RecoveryModel, CompatibilityLevel, ReadWriteDesc, OwnerName FROM #DummyDBDesc ORDER BY DBName
OPEN backupFiles
DECLARE @DBN varchar(100)
DECLARE @rm varchar(10)
DECLARE @cl varchar(30)
DECLARE @rwd varchar(10)
DECLARE @ownnamevarchar(100)
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @DBN, @rm, @cl, @rwd, @ownname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Database Name : ' + @DBN
PRINT 'Recovery Model : ' + @rm
PRINT 'Compatibility Level: ' + @cl
PRINT 'Read/Write : ' + @rwd
PRINT 'Database Owner : ' + @ownname
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_GROUPS''
THEN ''CREATE USER [''+ members.name + ''] FOR LOGIN [''+ members.name+ '']''
ELSE ''CREATE USER [''+ members.name + ''] FOR LOGIN [''+ members.name+ '']'' + '' WITH DEFAULT_SCHEMA=['' + members.default_schema_name + '']''
END AS CreateUser, '''' AS LineVal2, members.name AS UserName, 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 CreateUser'
--PRINT @cmd
EXEC (@cmd)
if exists( SELECT * FROM #dummyuserassign)
BEGIN
PRINT 'USE ' + @DBN
PRINT 'GO'
PRINT ' '
END
DECLARE myCursorVariable3 CURSOR FOR
SELECT LineVal,LineVal2,UserName, UserType FROM #dummyuserassign ORDER BY RecID
OPEN myCursorVariable3
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable3 INTO @lineval, @lineval2, @username, @usertype
WHILE @@FETCH_STATUS = 0
BEGIN
if @usertype = 'WINDOWS_USER'
BEGIN
PRINT '-- Login: ' + @username
PRINT 'if not exists(SELECT * FROM master.sys.server_Principals WHERE name = ''' + @username + ''')'
PRINT char(9) + char(9) + char(9) + 'CREATE LOGIN [' + @username + '] FROM WINDOWS WITH DEFAULT_DATABASE = [' + @DBN + ']'
END
PRINT @lineval
FETCH NEXT FROM myCursorVariable3 INTO @lineval, @lineval2, @username, @usertype
END
CLOSE myCursorVariable3
DEALLOCATE myCursorVariable3
END
INSERT INTO #dummyAllDBUsers
SELECT UserName, UserType, @DBN AS DBName FROM #dummyDBUsers
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)
/* ======================================================================================================================== */
-- added 01/08/2014 to do higher level permissions (like schema-wide permissions)
SET @cmd = 'USE [' + @DBN + ']; INSERT INTO #dummyDBPerms '
SET @cmd = @cmd + 'SELECT state_desc, permission_name, '''' as SchemaName, SCHEMA_NAME(major_id) AS Object, USER_NAME(grantee_principal_id) as Username, class_desc,
CONVERT(varchar(200),u.type_desc) AS UserType
FROM sys.database_permissions AS Perm
JOIN sys.database_principals AS Prin
ON Perm.major_ID = Prin.principal_id AND class_desc <> ''DATABASE''
JOIN sys.database_principals AS u
ON Perm.grantee_principal_id = u.principal_id
ORDER BY class_desc, SCHEMA_NAME(major_id), USER_NAME(grantee_principal_id)'
--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 CASE WHEN SchemaName <> '' THEN 1 ELSE 99 END,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
BEGIN
if @col5nm <> ''--if schema is not blank
PRINT @col1nm + ' ' + @col3nm + ' ON [' + @col5nm + '].[' + @col7nm + '] TO [' + @col9nm + '] --ObjectType=' + @col11nm + ' UserType=' + @col13nm
else
PRINT @col1nm + ' ' + @col3nm + ' ON ' + @col11nm + '::' + @col7nm + ' TO [' + @col9nm + '] --ObjectType=' + @col11nm + ' UserType=' + @col13nm
--GRANT SELECT ON SCHEMA::TestSchema TO TestUser
END
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, @ownname
END
CLOSE backupFiles
DEALLOCATE backupFiles
/* =============================================================================================== */
PRINT ' List of Principals that do not exist in any database'
PRINT ' (if user is assigned to databases for the instance user, then that user does not exist in '
PRINT ' individual database security settings)'
PRINT ' Further inclusion or continued accessibility should be confirmed'
PRINT ' '
--SELECT DISTINCT * FROM #dummyAllDBUsers ORDER BY UserName
--SELECT name, type_desc FROM master.sys.server_Principals
--SELECT * FROM master.sys.server_Principals
SET @maxnm = (SELECT MAX(LEN(name)) FROM master.sys.server_Principals WHERE name NOT IN ('sa','public','sysadmin','securityadmin','serveradmin','processadmin','bulkadmin','setupadmin','diskadmin','dbcreator'))
DECLARE myCursorVariable3 CURSOR FOR
SELECT name, type_desc, default_database_name FROM master.sys.server_Principals
WHERE name NOT IN ('sa','public','sysadmin','securityadmin','serveradmin','processadmin','bulkadmin','setupadmin','diskadmin','dbcreator')
OPEN myCursorVariable3
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable3 INTO @nm, @TD, @defdb
WHILE @@FETCH_STATUS = 0
BEGIN
--get rest of name after \ for windows users
if PATINDEX('%\%',@nm) > 0
SET @nmalt = ISNULL(SUBSTRING(@nm,LEN(@nm) - PATINDEX('%\%',REVERSE(@nm)) + 2,PATINDEX('%\%',REVERSE(@nm))-1),'')
else
SET @nmalt = @nm
if PATINDEX('%##%',@nm) <= 0
BEGIN
if (NOT EXISTS(SELECT * FROM #dummyAllDBUsers WHERE UserName = @nm)) AND (NOT EXISTS(SELECT * FROM #dummyAllDBUsers WHERE UserName LIKE '%' + @nmalt + '%'))
PRINT 'Principal ' + @nm + SPACE(@maxnm - LEN(@nm))+ ' does not exist in any databases (Default db = ' + @defdb + ')'
END
FETCH NEXT FROM myCursorVariable3 INTO @nm, @TD, @defdb
END
CLOSE myCursorVariable3
DEALLOCATE myCursorVariable3
/* =============================================================================================== */
--Dispose of the temporary tables
DROP TABLE #DummyDBDesc
DROP TABLE #dummyDBRoles
DROP TABLE #dummyDBUsers
DROP TABLE #dummyDBPerms
DROP TABLE #dummyuserassign
DROP TABLE #dummyrolelist
DROP TABLE #dummyAllDBUsers
PRINT '/* ====================================================================================================================== */'
PRINT '/* List of Orphaned Database Users */'
PRINT '/* (Users in databases with no associated instance login) */'
PRINT ' '
/* ====================================================================================================================== */
/* This section taken from
Finding orphaned database users
By Arthur Olcot in SQL Server Rocks! | 10-20-2011 7:47 PM
and modified.
http://www.sqlservercentral.com/blogs/rocks/archive/2011/10/20/finding-orphaned-database-users.aspx
*/
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE database_id > 4
OPEN cur
DECLARE @SQL NVARCHAR(MAX), @DBName SYSNAME
DECLARE @Results TABLE (DBName SYSNAME, UserName SYSNAME, UserSID VARBINARY(MAX))
DECLARE @dbn2 sysname
DECLARE @un2 SYSNAME
DECLARE @usid VARBINARY(MAX)
DECLARE @cmd2 varchar(4000)
DECLARE @maxlendb int
DECLARE @maxlenun int
FETCH NEXT FROM cur into @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE ' + @DBName + ';SELECT ''' + @DBName + ''' AS DBName,
UserName = name, UserSID = sid from sysusers
WHERE issqluser = 1 AND
(sid IS NOT NULL AND sid <> 0x0) AND
(LEN(sid) <= 16) AND SUSER_SNAME(sid) IS NULL'
INSERT INTO @Results
EXEC(@SQL)
FETCH NEXT FROM cur into @DBName
END
CLOSE cur
DEALLOCATE cur
--SELECT * FROM @Results
SET @maxlendb = (SELECT MAX(LEN(DBName)) FROM @Results)
SET @maxlenun = (SELECT MAX(LEN(UserName)) FROM @Results)
DECLARE myCursorVariable4 CURSOR FOR
SELECT DBName, UserName, UserSID FROM @Results
OPEN myCursorVariable4
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable4 INTO @dbn2, @un2, @usid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd2 = 'DB: ' + @dbn2 + SPACE(@maxlendb - LEN(@dbn2)) + ' User: ' + @un2 + SPACE(@maxlenun - LEN(@un2)) + ' is an orphan'
PRINT @cmd2
FETCH NEXT FROM myCursorVariable4 INTO @dbn2, @un2, @usid
END
CLOSE myCursorVariable4
DEALLOCATE myCursorVariable4
SET NOCOUNT OFF