Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Keeping roles and security in sync mirrored DB's Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 1:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:28 AM
Points: 68, Visits: 318
Hi

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

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

Thanks
Post #1462487
Posted Wednesday, June 12, 2013 4:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 1,379, Visits: 2,697
Since you can't mirror system databases which is where this info is stored - The best thing to do is to script out the logins and permissions. Do this on a regular basis.

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

And below, which I got from someone on this site

/* ====================================================================================================================== */
/* = Instance Security Audit Documentation = */
/* ====================================================================================================================== */
/* Created Date: 12/28/2011
By: VikingDBA
Modifications:
08/07/2012 Changed to also do Server Level Permissions (like VIEW ANY DATABASE, or VIEW SERVER STATE)
01/09/2013 Changed to print the scripts to create the CREATE ROLE and CREATE USER statements
01/31/2013 Changed to print the server level permission statements, and statements to set the default
databases for users

Dependencies:
This script depends on the following to exist:
none

Summary:
This script creates documentation for all databases in an instance, including the server level permissions, database role
permissions, and individual database object permissions.

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

NOTE: Runs for all databases, unless a specific database is used in the WHERE clause below.
Also, see User Settable Variables section below to see if any variables need to be set.
*/

Use MASTER

SET NOCOUNT ON


/* ==================================================================================================================== */
-- Security Audit for SERVER Roles
DECLARE @sr varchar(100)
DECLARE @mn varchar(150)
DECLARE @cmd varchar(4000)
DECLARE @col1nm varchar(200)
DECLARE @col2nm varchar(200)
DECLARE @col3nm varchar(200)
DECLARE @col4nm varchar(200)
DECLARE @col5nm varchar(200)
DECLARE @col6nm varchar(200)
DECLARE @col7nm varchar(200)
DECLARE @col8nm varchar(200)
DECLARE @col9nm varchar(200)
DECLARE @col10nm varchar(200)
DECLARE @col11nm varchar(200)
DECLARE @col12nm varchar(200)
DECLARE @col13nm varchar(200)
DECLARE @col14nm varchar(200)
DECLARE @col15nm varchar(200)
DECLARE @col16nm varchar(200)
DECLARE @col17nm varchar(200)
DECLARE @col18nm varchar(200)
DECLARE @col19nm varchar(200)
DECLARE @col20nm varchar(200)
DECLARE @col1len int
DECLARE @col2len int
DECLARE @col3len int
DECLARE @col4len int
DECLARE @col5len int
DECLARE @col6len int
DECLARE @col7len int
DECLARE @col8len int
DECLARE @col9len int
DECLARE @col10len int
DECLARE @col11len int
DECLARE @col12len int
DECLARE @col13len int
DECLARE @col14len int
DECLARE @col15len int
DECLARE @col16len int
DECLARE @col17len int
DECLARE @col18len int
DECLARE @col19len int
DECLARE @col20len int
DECLARE @col1max int
DECLARE @col2max int
DECLARE @col3max int
DECLARE @col4max int
DECLARE @col5max int
DECLARE @col6max int
DECLARE @col7max int
DECLARE @col8max int
DECLARE @col9max int
DECLARE @col10max int
DECLARE @col11max int
DECLARE @col12max int
DECLARE @col13max int
DECLARE @col14max int
DECLARE @col15max int
DECLARE @col16max int
DECLARE @col17max int
DECLARE @col18max int
DECLARE @col19max int
DECLARE @col20max int
DECLARE @col1min int
DECLARE @col2min int
DECLARE @col3min int
DECLARE @col4min int
DECLARE @col5min int
DECLARE @col6min int
DECLARE @col7min int
DECLARE @col8min int
DECLARE @col9min int
DECLARE @col10min int
DECLARE @col11min int
DECLARE @col12min int
DECLARE @col13min int
DECLARE @col14min int
DECLARE @col15min int
DECLARE @col16min int
DECLARE @col17min int
DECLARE @col18min int
DECLARE @col19min int
DECLARE @col20min int
DECLARE @rn varchar(200)
DECLARE @un varchar(200)
DECLARE @ut varchar(200)
DECLARE @sd varchar(200)
DECLARE @pn varchar(200)
DECLARE @sn varchar(200)
DECLARE @on varchar(200)
DECLARE @pd varchar(200)
DECLARE @sdmax int
DECLARE @pnmax int
DECLARE @snmax int
DECLARE @onmax int
DECLARE @pdmax int
DECLARE @unmax int
DECLARE @rnmax int
DECLARE @utmax int
DECLARE @outputtype int
DECLARE @prodlevel varchar(25)
DECLARE @version varchar(250)
DEClARE @prodver varchar(50)
DECLARE @edition varchar(50)
DECLARE @includeobjlvlperms bit
DECLARE @includeroleinfo bit
DECLARE @includedefaultdb bit
DECLARE @usnm varchar(128)
DECLARE @ustp varchar(60)
DECLARE @stdsc varchar(60)
DECLARE @permnm varchar(128)
DECLARE @collationname varchar(200)
DECLARE @lineval varchar(2000)
DECLARE @loginname varchar(100)
DECLARE @dbnametouse sysname

/* ================================================================================================ */
-- User Settable Variables
SET @outputtype = 1 -- 1=columnar 2=assignment statements
SET @includeobjlvlperms = 1
SET @includeroleinfo = 1
SET @includedefaultdb = 1
/* ================================================================================================ */

SELECT @prodlevel=CONVERT(varchar(25),SERVERPROPERTY('ProductLevel'))
SELECT @version=CONVERT(varchar(250),@@VERSION)
SELECT @prodver=CONVERT(varchar(50),SERVERPROPERTY('ProductVersion'))
SELECT @edition=CONVERT(varchar(50),SERVERPROPERTY('Edition'))
/* ============================================================================ */
--Find split out line
DECLARE @lvaltouse varchar(2000)
DECLARE @lvallength int
DECLARE @lvalct int
DECLARE @spotcat int
DECLARE @spotcatval int
DECLARE @lval1 varchar(2000)
DECLARE @lval2 varchar(2000)
DECLARE @lval3 varchar(2000)
DECLARE @lval4 varchar(2000)
DECLARE @lval5 varchar(2000)
DECLARE @lval6 varchar(2000)
SET @lvaltouse = @version
SET @lvallength = LEN(@lvaltouse)
SET @lvalct = 1
SET @spotcat = 1
SET @lval1 = ''
SET @lval2 = ''
SET @lval3 = ''
SET @lval4 = ''
SET @lval5 = ''
SET @lval6 = ''
WHILE @spotcat <= @lvallength
BEGIN
SET @spotcatval = ASCII(SUBSTRING(@lvaltouse,@spotcat,1))
if @spotcatval = 10 -- value we are looking for
SET @lvalct = @lvalct + 1 -- set to go to the next line and start building it
else -- add to current value line
BEGIN
if @spotcatval <> 9 -- values we are wanting to exclude
BEGIN
if @lvalct = 1
SET @lval1 = @lval1 + CHAR(@spotcatval)
if @lvalct = 2
SET @lval2 = @lval2 + CHAR(@spotcatval)
if @lvalct = 3
SET @lval3 = @lval3 + CHAR(@spotcatval)
if @lvalct = 4
SET @lval4 = @lval4 + CHAR(@spotcatval)
if @lvalct = 5
SET @lval5 = @lval5 + CHAR(@spotcatval)
if @lvalct = 6
SET @lval6 = @lval6 + CHAR(@spotcatval)
END
END
SET @spotcat = @spotcat + 1
END
--PRINT 'Line to split=' + @lvaltouse
--PRINT 'line1 = ' + @lval1
--PRINT 'line2 = ' + @lval2
--PRINT 'line3 = ' + @lval3
--PRINT 'line4 = ' + @lval4
--PRINT 'line5 = ' + @lval5
--PRINT 'line6 = ' + @lval6
/* ============================================================================= */
CREATE TABLE #dummyuserassign
(RecID int IDENTITY,
LineVal varchar(2000)
)

PRINT '============================================================================================================='
PRINT ' Security Audit For Server Instance ' + CONVERT(varchar(128),@@servername)
if @outputtype = 2
PRINT ' Assignment Statements'
PRINT ' For ' + CONVERT(varchar(128),getdate(),101) + ' ' + CONVERT(varchar(128),getdate(),108)
PRINT '============================================================================================================='
PRINT 'SQL Server Version: ' + @lval1
PRINT ' ' + @lval4
PRINT '============================================================================================================='
PRINT 'NOTE: Make sure to get list of logins using the sp_help_revlogin stored procedure in the master database.'
PRINT '============================================================================================================='
PRINT ' Server Role Security Settings'
PRINT ' '
PRINT ' '




CREATE TABLE #rolememberdummy
(ServerRole varchar(100),
MemberName varchar(150),
MemberSID varchar(2000)
)
CREATE TABLE #dummyDBPerms
( StateDesc varchar(200),
PermName varchar(200),
SchemaName varchar(200),
ObjectName varchar(200),
UserName varchar(200),
ObjectType varchar(200),
UserType varchar(200)
)


-- Security Audit
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'sysadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'securityadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'serveradmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'dbcreator'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'diskadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'processadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'setupadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'bulkadmin'



SET @col1nm = 'Role'
SET @col1len = 20
SET @col2nm = ''
SET @col2len = 8
SET @col3nm = 'Member Name'
SET @col3len = 30
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm
PRINT REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len)






--SELECT CONVERT(varchar(30),ServerRole) as ServerRole, CONVERT(varchar(30),MemberName) AS MemberName FROM #rolememberdummy
DECLARE backupFiles CURSOR FOR
SELECT ServerRole, MemberName FROM #rolememberdummy

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @sr, @mn

WHILE @@FETCH_STATUS = 0
BEGIN
SET @col1nm = @sr
SET @col1len = 20
SET @col2nm = ''
SET @col2len = 8
SET @col3nm = @mn
SET @col3len = 30
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm


FETCH NEXT FROM backupFiles INTO @sr, @mn
END

CLOSE backupFiles
DEALLOCATE backupFiles

DROP TABLE #rolememberdummy


PRINT ' '
PRINT ' '
PRINT '==========================================================================================================='
PRINT ' Server Level Permissions'
PRINT ' '
PRINT ' '

CREATE TABLE #serverpermdummy
(UserName varchar(128),
UserType varchar(60),
StateDesc varchar(60),
PermName varchar(128)
)

INSERT INTO #serverpermdummy
SELECT l.name as UserName, l.type_desc AS UserType, p.state_desc AS StateDesc, p.permission_name AS PermName
FROM sys.server_permissions AS p
JOIN sys.server_principals AS l ON p.grantee_principal_id = l.principal_id
WHERE ((permission_name <> 'CONNECT SQL' AND permission_name <> 'CONNECT') OR p.state_desc = 'DENY') AND l.type_desc <> 'CERTIFICATE_MAPPED_LOGIN' AND l.name NOT LIKE '%##MS_%'
ORDER BY l.principal_id

--SELECT * FROM sys.server_principals

SET @col1nm = 'User Name'
SET @col1len = 20
SET @col2nm = ''
SET @col2len = 8
SET @col3nm = 'User Type'
SET @col3len = 20
SET @col4nm = ''
SET @col4len = 8
SET @col5nm = 'State Desc'
SET @col5len = 20
SET @col6nm = ''
SET @col6len = 8
SET @col7nm = 'Permission'
SET @col7len = 30

SET @col1min = LEN(@col1nm)
SET @col3min = LEN(@col3nm)
SET @col5min = LEN(@col5nm)
SET @col7min = LEN(@col7nm)

--Get the length of the longest occurance of the columns
SELECT @col1max = ISNULL(MAX(len(LTRIM(RTRIM(UserName)))),0) FROM #serverpermdummy
SELECT @col3max = ISNULL(MAX(len(LTRIM(RTRIM(UserType)))),0) FROM #serverpermdummy
SELECT @col5max = ISNULL(MAX(len(LTRIM(RTRIM(StateDesc)))),0) FROM #serverpermdummy
SELECT @col7max = ISNULL(MAX(len(LTRIM(RTRIM(PermName)))),0) FROM #serverpermdummy


--Set some minimum values so column doesn't print short
if @col1max < @col1min SET @col1len = @col1min else SET @col1len = @col1max
if @col3max < @col3min SET @col3len = @col3min else SET @col3len = @col3max
if @col5max < @col5min SET @col5len = @col5min else SET @col5len = @col5max
if @col7max < @col7min SET @col7len = @col7min else SET @col7len = @col7max

if @outputtype = 1
BEGIN
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len) + @col7nm + SPACE(@col7len-len(@col7nm))
PRINT REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len) + SPACE(@col4len) + REPLICATE('=',@col5len) + SPACE(@col6len) + REPLICATE('=',@col7len)
END
else
if EXISTS (SELECT ISNULL(UserName,'') AS UserName, ISNULL(UserType,'') AS UserType, ISNULL(StateDesc,'') AS StateDesc, ISNULL(PermName,'') AS PermName FROM #serverpermdummy)
PRINT 'USE master;'


DECLARE backupFiles CURSOR FOR
SELECT ISNULL(UserName,'') AS UserName, ISNULL(UserType,'') AS UserType, ISNULL(StateDesc,'') AS StateDesc, ISNULL(PermName,'') AS PermName FROM #serverpermdummy

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @usnm, @ustp, @stdsc, @permnm


WHILE @@FETCH_STATUS = 0
BEGIN
SET @col1nm = @usnm
SET @col2nm = ''
SET @col3nm = @ustp
SET @col4nm = ''
SET @col5nm = @stdsc
SET @col6nm = ''
SET @col7nm = @permnm
if @outputtype = 1
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len) + @col7nm + SPACE(@col7len-len(@col7nm))
else
PRINT @stdsc + ' ' + @permnm + ' TO ' + @usnm + ';'


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

CLOSE backupFiles
DEALLOCATE backupFiles


DROP TABLE #serverpermdummy

PRINT ' '
PRINT ' '
PRINT '==========================================================================================================='
PRINT ' Information By Database'
PRINT ' '
PRINT ' '

CREATE TABLE #DummyDBDesc
( RecID int IDENTITY NOT NULL,
ServerName varchar(128) NULL,
DBName varchar(100) NULL,
RecoveryModel varchar(10) NULL,
CompatibilityLevel varchar(30) NULL,
ReadWriteDesc varchar(10) NULL
)
CREATE TABLE #dummyDBRoles
( RoleName varchar(200),
UserName varchar(200),
UserType varchar(200)
)

CREATE TABLE #dummyrolelist
( RoleName varchar(200)
)

CREATE TABLE #dummyDBUsers
( UserName varchar(200),
UserType varchar(200)
)
INSERT INTO #DummyDBDesc
select CONVERT(varchar(128),@@servername) AS ServerName, CONVERT(varchar(100),name) as DBName, CONVERT(varchar(10),recovery_model_desc) as RecoveryModel, --database_id,
CASE compatibility_level
WHEN 80 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2000 *'
WHEN 90 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2005'
WHEN 100 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2008'
WHEN 105 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2008 R2'
WHEN 110 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2012'
ELSE CONVERT(varchar(4),compatibility_level)
END AS CompatibilityLevel,
CASE is_read_only
WHEN 0 THEN CONVERT(varchar(10),'RW')
ELSE CONVERT(varchar(10),'R')
END as ReadWriteDesc
FROM sys.databases
WHERE name NOT IN('tempdb','master','msdb','model') and name NOT LIKE '%ReportServer%' AND state = 0
--AND name = 'MyDatabase'
ORDER BY name


DECLARE backupFiles CURSOR FOR
SELECT DBName, RecoveryModel, CompatibilityLevel, ReadWriteDesc FROM #DummyDBDesc ORDER BY DBName
OPEN backupFiles

DECLARE @dbn varchar(100)
DECLARE @rm varchar(10)
DECLARE @cl varchar(30)
DECLARE @rwd varchar(10)


-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @dbn, @rm, @cl, @rwd

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'Database Name : ' + @dbn
PRINT 'Recovery Model : ' + @rm
PRINT 'Compatibility Level: ' + @cl
PRINT 'Read/Write : ' + @rwd
PRINT ' '
PRINT ' '


/* ================================================================================================================================================================= */
/* Database User Information */

--Start with a clean table to load the values
TRUNCATE TABLE #dummyDBUsers

-- Get roles for this database and load into the temp table
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBUsers SELECT CONVERT(varchar(100),name) AS UserName, CONVERT(varchar(100),type_desc) as UserType FROM sys.database_principals WHERE (type = ''S'' OR type = ''U'' OR type = ''G'') AND is_fixed_role = 0 AND (name NOT IN (''guest'',''dbo'',''INFORMATION_SCHEMA'',''sys''))'
--PRINT @cmd
EXEC (@cmd)

--Get the length of the longest occurance of the columns
SELECT @unmax = ISNULL(MAX(len(UserName)),0) FROM #dummyDBUsers
SELECT @utmax = ISNULL(MAX(len(UserType)),0) FROM #dummyDBUsers

--Set some minimum values so column doesn't print short
if @unmax < 25 SET @unmax = 25
if @utmax < 25 SET @utmax = 25

--Set and print the column headings for the role information
SET @col1nm = 'UserName'
SET @col1len = @unmax
SET @col2nm = ''
SET @col2len = 5
SET @col3nm = 'UserType'
SET @col3len = @utmax
PRINT ' '
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm))
PRINT SPACE(10) + REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len)

DECLARE backupFiles2 CURSOR FOR
SELECT UserName, UserType FROM #dummyDBUsers ORDER BY UserName

OPEN backupFiles2

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles2 INTO @un, @ut

WHILE @@FETCH_STATUS = 0
BEGIN
--Set and print the row details for the role information
SET @col1nm = SUBSTRING(@un,1,@unmax)
SET @col3nm = SUBSTRING(@ut,1,@utmax)

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

FETCH NEXT FROM backupFiles2 INTO @un, @ut
END

CLOSE backupFiles2
DEALLOCATE backupFiles2

PRINT ' '
PRINT ' '

if @outputtype = 2 -- create the statements to assign a user to this database
BEGIN
TRUNCATE TABLE #dummyuserassign

SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyuserassign select DISTINCT
CASE members.type_desc
WHEN ''WINDOWS_USER''
THEN ''CREATE USER [''+ members.name + ''] FOR LOGIN [''+ members.name+ '']'' + '' WITH DEFAULT_SCHEMA=['' + members.default_schema_name + '']''
WHEN ''SQL_USER''
THEN ''CREATE USER [''+ members.name + ''] FOR LOGIN [''+ members.name+ '']'' + '' WITH DEFAULT_SCHEMA=['' + members.default_schema_name + '']''
END AS CreateUser
from sys.database_principals members
inner join sys.database_role_members drm
on members.principal_id = drm.member_principal_id
inner join sys.database_principals roles
on drm.role_principal_id = roles.principal_id
where members.name <> ''dbo''
ORDER BY CreateUser'
--PRINT @cmd
EXEC (@cmd)


if exists( SELECT * FROM #dummyuserassign)
BEGIN
PRINT 'USE ' + @dbn
PRINT 'GO'
PRINT ' '
END
DECLARE myCursorVariable3 CURSOR FOR
SELECT LineVal FROM #dummyuserassign ORDER BY RecID

OPEN myCursorVariable3

-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable3 INTO @lineval

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @lineval

FETCH NEXT FROM myCursorVariable3 INTO @lineval
END

CLOSE myCursorVariable3
DEALLOCATE myCursorVariable3
END



if @includeroleinfo = 1
BEGIN
/* ================================================================================================================================================================= */
/* Role Information */
SELECT @collationname = collation_name FROM master.sys.databases WHERE name = @dbn
if @collationname IS NULL
print 'null for ' + @dbn

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

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

--Start with a clean table to load the values
TRUNCATE TABLE #dummyDBRoles

-- Get roles for this database and load into the temp table
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBRoles select CONVERT(varchar(200),roles.name) AS RoleName, CONVERT(varchar(200),members.name) AS UserName, CONVERT(varchar(200),members.type_desc) AS UserType from sys.database_principals members inner join sys.database_role_members drm on members.principal_id = drm.member_principal_id inner join sys.database_principals roles on drm.role_principal_id = roles.principal_id where members.name <> ''dbo'' ORDER BY members.name, roles.name'
--PRINT @cmd
EXEC (@cmd)

-- Now add in any roles that are present in the database that do not have anyone assigned to them (those that are already in the temp table)
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBRoles SELECT CONVERT(varchar(200),name) AS RoleName, ''--none--'' As UserName, '''' AS UserType FROM sys.database_principals WHERE type = ''R'' and is_fixed_role = 0 and name <> ''public'' AND (name NOT IN (SELECT RoleName FROM #dummyDBRoles))'
--PRINT @cmd
EXEC (@cmd)

-- now get a list of database roles that were created and print them as CREATE ROLE statements
if @outputtype = 2
BEGIN
TRUNCATE TABLE #dummyrolelist
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyrolelist SELECT name FROM sys.database_principals WHERE type = ''R'' AND name <> ''public'' AND is_fixed_role = 0'
--PRINT @cmd
EXEC (@cmd)
PRINT 'USE ' + @dbn
PRINT 'GO'
PRINT ' '
DECLARE myCursorVariable4 CURSOR FOR
SELECT RoleName FROM #dummyrolelist

OPEN myCursorVariable4

-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable4 INTO @rn

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'CREATE ROLE ' + @rn
PRINT @cmd


FETCH NEXT FROM myCursorVariable4 INTO @rn
END

CLOSE myCursorVariable4
DEALLOCATE myCursorVariable4
END

--Get the length of the longest occurance of the columns
SELECT @rnmax = ISNULL(MAX(len(RoleName)),0) FROM #dummyDBRoles
SELECT @unmax = ISNULL(MAX(len(UserName)),0) FROM #dummyDBRoles
SELECT @utmax = ISNULL(MAX(len(UserType)),0) FROM #dummyDBRoles

--Set some minimum values so column doesn't print short
if @rnmax < 25 SET @rnmax = 25
if @unmax < 25 SET @unmax = 25
if @utmax < 25 SET @utmax = 25

--Set and print the column headings for the role information
SET @col1nm = 'RoleName'
SET @col1len = @rnmax
SET @col2nm = ''
SET @col2len = 5
SET @col3nm = 'UserName'
SET @col3len = @unmax
SET @col4nm = ''
SET @col4len = 5
SET @col5nm = 'UserType'
SET @col5len = @utmax
PRINT ' '
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm))
PRINT SPACE(10) + REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len) + SPACE(@col4len) + REPLICATE('=',@col5len)

-- Print the script to set the database context
if @outputtype = 2
BEGIN
PRINT 'USE ' + @dbn
PRINT 'GO'
PRINT ' '
END

--statement to get all roles for this database
--SELECT name FROM sys.database_principals WHERE type = 'R' and is_fixed_role = 0 and name <> 'public'
--can use to script the CREATE ROLE statements

-- Now loop through the roles
DECLARE backupFiles2 CURSOR FOR
SELECT RoleName, UserName, UserType FROM #dummyDBRoles ORDER BY RoleName

OPEN backupFiles2

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles2 INTO @rn, @un, @ut

WHILE @@FETCH_STATUS = 0
BEGIN
--Set and print the row details for the role information
SET @col1nm = SUBSTRING(@rn,1,@rnmax)
SET @col3nm = SUBSTRING(@un,1,@unmax)
SET @col5nm = SUBSTRING(@ut,1,@utmax)
if @outputtype = 1
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm))
if @outputtype = 2
BEGIN
if @col3nm <> '--none--'
PRINT 'exec sp_addrolemember [' + @col1nm + '], [' + @col3nm + '] --Usertype= ' + @col5nm
else
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm))
END

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

CLOSE backupFiles2
DEALLOCATE backupFiles2

PRINT ' '
PRINT ' '
END


if @includeobjlvlperms = 1
BEGIN
/* ================================================================================================================================================================= */
/* Object-Level Permissions Information */
--Start with a clean table to load the values
TRUNCATE TABLE #dummyDBPerms

-- Get permissions for this database and load into the temp table
-- I'm sure some of this part came from elsewhere. My appologies to the originator.
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBPerms '
SET @cmd = @cmd + 'select p.state_desc, p.permission_name, s.name, o.name, u.name, CASE o.type WHEN ''P'' THEN ''SPROC''
WHEN ''V'' THEN ''View''
WHEN ''U'' THEN ''Table''
WHEN ''FN'' THEN ''Function (scaler)''
WHEN ''TF'' THEN ''Function (table-valued)''
ELSE o.type_desc END AS ObjectType,
CONVERT(varchar(200),u.type_desc) AS UserType
from sys.database_permissions p
inner join sys.objects o on p.major_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
ORDER BY o.type, o.name collate Latin1_general_CI_AS, u.name collate Latin1_general_CI_AS'
--PRINT @cmd
EXEC (@cmd)

--Get the length of the longest occurance of each of the columns
SELECT @sdmax = ISNULL(MAX(len(StateDesc)),0) FROM #dummyDBPerms
SELECT @pnmax = ISNULL(MAX(len(PermName)),0) FROM #dummyDBPerms
SELECT @snmax = ISNULL(MAX(len(SchemaName)),0) FROM #dummyDBPerms
SELECT @onmax = ISNULL(MAX(len(ObjectName)),0) FROM #dummyDBPerms
SELECT @unmax = ISNULL(MAX(len(UserName)),0) FROM #dummyDBPerms
SELECT @pdmax = ISNULL(MAX(len(ObjectType)),0) FROM #dummyDBPerms
SELECT @utmax = ISNULL(MAX(len(UserType)),0) FROM #dummyDBPerms

--Set some minimum values so column doesn't print short
if @sdmax < 15 SET @sdmax = 15
if @pnmax < 15 SET @pnmax = 15
if @snmax < 10 SET @snmax = 10
if @onmax < 15 SET @onmax = 15
if @unmax < 15 SET @unmax = 15
if @pdmax < 15 SET @pdmax = 15 --ObjectType
if @utmax < 15 SET @utmax = 15 --UserType

--Set and print the column headings for the permissions information
SET @col1nm = 'StateDesc'
SET @col1len = @sdmax
SET @col2nm = ''
SET @col2len = 5
SET @col3nm = 'PermName'
SET @col3len = @pnmax
SET @col4nm = ''
SET @col4len = 5
SET @col5nm = 'Schema'
SET @col5len = @snmax
SET @col6nm = ''
SET @col6len = 5
SET @col7nm = 'Object'
SET @col7len = @onmax
SET @col8nm = ''
SET @col8len = 5
SET @col9nm = 'User'
SET @col9len = @unmax
SET @col10nm = ''
SET @col10len = 5
SET @col11nm = 'ObjectType'
SET @col11len = @pdmax
SET @col12nm = ''
SET @col12len = 5
SET @col13nm = 'UserType'
SET @col13len = @utmax

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

--Loop through the permissions for this database and format and print them
DECLARE backupFiles2 CURSOR FOR
SELECT StateDesc,PermName,SchemaName,ObjectName,UserName,ObjectType,UserType FROM #dummyDBPerms ORDER BY Schemaname,ObjectName,UserName

OPEN backupFiles2

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles2 INTO @sd, @pn, @sn, @on, @un, @pd, @ut

WHILE @@FETCH_STATUS = 0
BEGIN
--Set and print the row details for the permissions information
SET @col1nm = SUBSTRING(@sd,1,@sdmax)
SET @col3nm = SUBSTRING(@pn,1,@pnmax)
SET @col5nm = SUBSTRING(@sn,1,@snmax)
SET @col7nm = SUBSTRING(@on,1,@onmax)
SET @col9nm = SUBSTRING(@un,1,@unmax)
SET @col11nm = SUBSTRING(@pd,1,@pdmax)
SET @col13nm = SUBSTRING(@ut,1,@utmax)

--print the detail record for the permissions
if @outputtype = 1
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len)+ @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len)+ @col7nm + SPACE(@col7len-len(@col7nm)) + SPACE(@col8len) + @col9nm + SPACE(@col9len-len(@col9nm)) + SPACE(@col10len) + @col11nm + SPACE(@col11len-len(@col11nm)) + SPACE(@col12len) + @col13nm + SPACE(@col13len-len(@col13nm))
if @outputtype = 2
PRINT @col1nm + ' ' + @col3nm + ' ON [' + @col5nm + '].[' + @col7nm + '] TO [' + @col9nm + '] --ObjectType=' + @col11nm + ' UserType=' + @col13nm


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

CLOSE backupFiles2
DEALLOCATE backupFiles2

PRINT ' '
PRINT ' '

END


if @outputtype = 2 AND @includedefaultdb = 1
BEGIN
if EXISTS (SELECT name FROM master.sys.server_Principals WHERE type in ('G','S','U') AND default_database_name = @dbn)
BEGIN
PRINT ' '
PRINT ' '
PRINT '-- Here are the logins and their default database settings'
PRINT ' '

DECLARE myCursorVariable CURSOR FOR
SELECT name, default_database_name as DefaultDB
FROM master.sys.server_Principals
WHERE type in ('G','S','U') AND default_database_name = @dbn
ORDER BY name

OPEN myCursorVariable

-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ALTER LOGIN [' + @loginname + '] WITH DEFAULT_DATABASE = ' + @dbnametouse

FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse
END

CLOSE myCursorVariable
DEALLOCATE myCursorVariable

PRINT ' '
PRINT ' '
END
END




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

--Get the next database name and info to use in the database loop
FETCH NEXT FROM backupFiles INTO @dbn, @rm, @cl, @rwd
END

CLOSE backupFiles
DEALLOCATE backupFiles

/* =============================================================================================== */
--Dispose of the temporary tables
DROP TABLE #DummyDBDesc
DROP TABLE #dummyDBRoles
DROP TABLE #dummyDBUsers
DROP TABLE #dummyDBPerms
DROP TABLE #dummyuserassign
DROP TABLE #dummyrolelist

SET NOCOUNT OFF

Post #1462540
Posted Thursday, June 13, 2013 1:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:28 AM
Points: 68, Visits: 318
SQLSACT (6/12/2013)
Since you can't mirror system databases which is where this info is stored - The best thing to do is to script out the logins and permissions. Do this on a regular basis.

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

And below, which I got from someone on this site

/* ====================================================================================================================== */
/* = Instance Security Audit Documentation = */
/* ====================================================================================================================== */
/* Created Date: 12/28/2011
By: VikingDBA
Modifications:
08/07/2012 Changed to also do Server Level Permissions (like VIEW ANY DATABASE, or VIEW SERVER STATE)
01/09/2013 Changed to print the scripts to create the CREATE ROLE and CREATE USER statements
01/31/2013 Changed to print the server level permission statements, and statements to set the default
databases for users

Dependencies:
This script depends on the following to exist:
none

Summary:
This script creates documentation for all databases in an instance, including the server level permissions, database role
permissions, and individual database object permissions.

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

NOTE: Runs for all databases, unless a specific database is used in the WHERE clause below.
Also, see User Settable Variables section below to see if any variables need to be set.
*/

Use MASTER

SET NOCOUNT ON


/* ==================================================================================================================== */
-- Security Audit for SERVER Roles
DECLARE @sr varchar(100)
DECLARE @mn varchar(150)
DECLARE @cmd varchar(4000)
DECLARE @col1nm varchar(200)
DECLARE @col2nm varchar(200)
DECLARE @col3nm varchar(200)
DECLARE @col4nm varchar(200)
DECLARE @col5nm varchar(200)
DECLARE @col6nm varchar(200)
DECLARE @col7nm varchar(200)
DECLARE @col8nm varchar(200)
DECLARE @col9nm varchar(200)
DECLARE @col10nm varchar(200)
DECLARE @col11nm varchar(200)
DECLARE @col12nm varchar(200)
DECLARE @col13nm varchar(200)
DECLARE @col14nm varchar(200)
DECLARE @col15nm varchar(200)
DECLARE @col16nm varchar(200)
DECLARE @col17nm varchar(200)
DECLARE @col18nm varchar(200)
DECLARE @col19nm varchar(200)
DECLARE @col20nm varchar(200)
DECLARE @col1len int
DECLARE @col2len int
DECLARE @col3len int
DECLARE @col4len int
DECLARE @col5len int
DECLARE @col6len int
DECLARE @col7len int
DECLARE @col8len int
DECLARE @col9len int
DECLARE @col10len int
DECLARE @col11len int
DECLARE @col12len int
DECLARE @col13len int
DECLARE @col14len int
DECLARE @col15len int
DECLARE @col16len int
DECLARE @col17len int
DECLARE @col18len int
DECLARE @col19len int
DECLARE @col20len int
DECLARE @col1max int
DECLARE @col2max int
DECLARE @col3max int
DECLARE @col4max int
DECLARE @col5max int
DECLARE @col6max int
DECLARE @col7max int
DECLARE @col8max int
DECLARE @col9max int
DECLARE @col10max int
DECLARE @col11max int
DECLARE @col12max int
DECLARE @col13max int
DECLARE @col14max int
DECLARE @col15max int
DECLARE @col16max int
DECLARE @col17max int
DECLARE @col18max int
DECLARE @col19max int
DECLARE @col20max int
DECLARE @col1min int
DECLARE @col2min int
DECLARE @col3min int
DECLARE @col4min int
DECLARE @col5min int
DECLARE @col6min int
DECLARE @col7min int
DECLARE @col8min int
DECLARE @col9min int
DECLARE @col10min int
DECLARE @col11min int
DECLARE @col12min int
DECLARE @col13min int
DECLARE @col14min int
DECLARE @col15min int
DECLARE @col16min int
DECLARE @col17min int
DECLARE @col18min int
DECLARE @col19min int
DECLARE @col20min int
DECLARE @rn varchar(200)
DECLARE @un varchar(200)
DECLARE @ut varchar(200)
DECLARE @sd varchar(200)
DECLARE @pn varchar(200)
DECLARE @sn varchar(200)
DECLARE @on varchar(200)
DECLARE @pd varchar(200)
DECLARE @sdmax int
DECLARE @pnmax int
DECLARE @snmax int
DECLARE @onmax int
DECLARE @pdmax int
DECLARE @unmax int
DECLARE @rnmax int
DECLARE @utmax int
DECLARE @outputtype int
DECLARE @prodlevel varchar(25)
DECLARE @version varchar(250)
DEClARE @prodver varchar(50)
DECLARE @edition varchar(50)
DECLARE @includeobjlvlperms bit
DECLARE @includeroleinfo bit
DECLARE @includedefaultdb bit
DECLARE @usnm varchar(128)
DECLARE @ustp varchar(60)
DECLARE @stdsc varchar(60)
DECLARE @permnm varchar(128)
DECLARE @collationname varchar(200)
DECLARE @lineval varchar(2000)
DECLARE @loginname varchar(100)
DECLARE @dbnametouse sysname

/* ================================================================================================ */
-- User Settable Variables
SET @outputtype = 1 -- 1=columnar 2=assignment statements
SET @includeobjlvlperms = 1
SET @includeroleinfo = 1
SET @includedefaultdb = 1
/* ================================================================================================ */

SELECT @prodlevel=CONVERT(varchar(25),SERVERPROPERTY('ProductLevel'))
SELECT @version=CONVERT(varchar(250),@@VERSION)
SELECT @prodver=CONVERT(varchar(50),SERVERPROPERTY('ProductVersion'))
SELECT @edition=CONVERT(varchar(50),SERVERPROPERTY('Edition'))
/* ============================================================================ */
--Find split out line
DECLARE @lvaltouse varchar(2000)
DECLARE @lvallength int
DECLARE @lvalct int
DECLARE @spotcat int
DECLARE @spotcatval int
DECLARE @lval1 varchar(2000)
DECLARE @lval2 varchar(2000)
DECLARE @lval3 varchar(2000)
DECLARE @lval4 varchar(2000)
DECLARE @lval5 varchar(2000)
DECLARE @lval6 varchar(2000)
SET @lvaltouse = @version
SET @lvallength = LEN(@lvaltouse)
SET @lvalct = 1
SET @spotcat = 1
SET @lval1 = ''
SET @lval2 = ''
SET @lval3 = ''
SET @lval4 = ''
SET @lval5 = ''
SET @lval6 = ''
WHILE @spotcat <= @lvallength
BEGIN
SET @spotcatval = ASCII(SUBSTRING(@lvaltouse,@spotcat,1))
if @spotcatval = 10 -- value we are looking for
SET @lvalct = @lvalct + 1 -- set to go to the next line and start building it
else -- add to current value line
BEGIN
if @spotcatval <> 9 -- values we are wanting to exclude
BEGIN
if @lvalct = 1
SET @lval1 = @lval1 + CHAR(@spotcatval)
if @lvalct = 2
SET @lval2 = @lval2 + CHAR(@spotcatval)
if @lvalct = 3
SET @lval3 = @lval3 + CHAR(@spotcatval)
if @lvalct = 4
SET @lval4 = @lval4 + CHAR(@spotcatval)
if @lvalct = 5
SET @lval5 = @lval5 + CHAR(@spotcatval)
if @lvalct = 6
SET @lval6 = @lval6 + CHAR(@spotcatval)
END
END
SET @spotcat = @spotcat + 1
END
--PRINT 'Line to split=' + @lvaltouse
--PRINT 'line1 = ' + @lval1
--PRINT 'line2 = ' + @lval2
--PRINT 'line3 = ' + @lval3
--PRINT 'line4 = ' + @lval4
--PRINT 'line5 = ' + @lval5
--PRINT 'line6 = ' + @lval6
/* ============================================================================= */
CREATE TABLE #dummyuserassign
(RecID int IDENTITY,
LineVal varchar(2000)
)

PRINT '============================================================================================================='
PRINT ' Security Audit For Server Instance ' + CONVERT(varchar(128),@@servername)
if @outputtype = 2
PRINT ' Assignment Statements'
PRINT ' For ' + CONVERT(varchar(128),getdate(),101) + ' ' + CONVERT(varchar(128),getdate(),108)
PRINT '============================================================================================================='
PRINT 'SQL Server Version: ' + @lval1
PRINT ' ' + @lval4
PRINT '============================================================================================================='
PRINT 'NOTE: Make sure to get list of logins using the sp_help_revlogin stored procedure in the master database.'
PRINT '============================================================================================================='
PRINT ' Server Role Security Settings'
PRINT ' '
PRINT ' '




CREATE TABLE #rolememberdummy
(ServerRole varchar(100),
MemberName varchar(150),
MemberSID varchar(2000)
)
CREATE TABLE #dummyDBPerms
( StateDesc varchar(200),
PermName varchar(200),
SchemaName varchar(200),
ObjectName varchar(200),
UserName varchar(200),
ObjectType varchar(200),
UserType varchar(200)
)


-- Security Audit
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'sysadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'securityadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'serveradmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'dbcreator'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'diskadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'processadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'setupadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'bulkadmin'



SET @col1nm = 'Role'
SET @col1len = 20
SET @col2nm = ''
SET @col2len = 8
SET @col3nm = 'Member Name'
SET @col3len = 30
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm
PRINT REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len)






--SELECT CONVERT(varchar(30),ServerRole) as ServerRole, CONVERT(varchar(30),MemberName) AS MemberName FROM #rolememberdummy
DECLARE backupFiles CURSOR FOR
SELECT ServerRole, MemberName FROM #rolememberdummy

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @sr, @mn

WHILE @@FETCH_STATUS = 0
BEGIN
SET @col1nm = @sr
SET @col1len = 20
SET @col2nm = ''
SET @col2len = 8
SET @col3nm = @mn
SET @col3len = 30
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm


FETCH NEXT FROM backupFiles INTO @sr, @mn
END

CLOSE backupFiles
DEALLOCATE backupFiles

DROP TABLE #rolememberdummy


PRINT ' '
PRINT ' '
PRINT '==========================================================================================================='
PRINT ' Server Level Permissions'
PRINT ' '
PRINT ' '

CREATE TABLE #serverpermdummy
(UserName varchar(128),
UserType varchar(60),
StateDesc varchar(60),
PermName varchar(128)
)

INSERT INTO #serverpermdummy
SELECT l.name as UserName, l.type_desc AS UserType, p.state_desc AS StateDesc, p.permission_name AS PermName
FROM sys.server_permissions AS p
JOIN sys.server_principals AS l ON p.grantee_principal_id = l.principal_id
WHERE ((permission_name <> 'CONNECT SQL' AND permission_name <> 'CONNECT') OR p.state_desc = 'DENY') AND l.type_desc <> 'CERTIFICATE_MAPPED_LOGIN' AND l.name NOT LIKE '%##MS_%'
ORDER BY l.principal_id

--SELECT * FROM sys.server_principals

SET @col1nm = 'User Name'
SET @col1len = 20
SET @col2nm = ''
SET @col2len = 8
SET @col3nm = 'User Type'
SET @col3len = 20
SET @col4nm = ''
SET @col4len = 8
SET @col5nm = 'State Desc'
SET @col5len = 20
SET @col6nm = ''
SET @col6len = 8
SET @col7nm = 'Permission'
SET @col7len = 30

SET @col1min = LEN(@col1nm)
SET @col3min = LEN(@col3nm)
SET @col5min = LEN(@col5nm)
SET @col7min = LEN(@col7nm)

--Get the length of the longest occurance of the columns
SELECT @col1max = ISNULL(MAX(len(LTRIM(RTRIM(UserName)))),0) FROM #serverpermdummy
SELECT @col3max = ISNULL(MAX(len(LTRIM(RTRIM(UserType)))),0) FROM #serverpermdummy
SELECT @col5max = ISNULL(MAX(len(LTRIM(RTRIM(StateDesc)))),0) FROM #serverpermdummy
SELECT @col7max = ISNULL(MAX(len(LTRIM(RTRIM(PermName)))),0) FROM #serverpermdummy


--Set some minimum values so column doesn't print short
if @col1max < @col1min SET @col1len = @col1min else SET @col1len = @col1max
if @col3max < @col3min SET @col3len = @col3min else SET @col3len = @col3max
if @col5max < @col5min SET @col5len = @col5min else SET @col5len = @col5max
if @col7max < @col7min SET @col7len = @col7min else SET @col7len = @col7max

if @outputtype = 1
BEGIN
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len) + @col7nm + SPACE(@col7len-len(@col7nm))
PRINT REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len) + SPACE(@col4len) + REPLICATE('=',@col5len) + SPACE(@col6len) + REPLICATE('=',@col7len)
END
else
if EXISTS (SELECT ISNULL(UserName,'') AS UserName, ISNULL(UserType,'') AS UserType, ISNULL(StateDesc,'') AS StateDesc, ISNULL(PermName,'') AS PermName FROM #serverpermdummy)
PRINT 'USE master;'


DECLARE backupFiles CURSOR FOR
SELECT ISNULL(UserName,'') AS UserName, ISNULL(UserType,'') AS UserType, ISNULL(StateDesc,'') AS StateDesc, ISNULL(PermName,'') AS PermName FROM #serverpermdummy

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @usnm, @ustp, @stdsc, @permnm


WHILE @@FETCH_STATUS = 0
BEGIN
SET @col1nm = @usnm
SET @col2nm = ''
SET @col3nm = @ustp
SET @col4nm = ''
SET @col5nm = @stdsc
SET @col6nm = ''
SET @col7nm = @permnm
if @outputtype = 1
PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len) + @col7nm + SPACE(@col7len-len(@col7nm))
else
PRINT @stdsc + ' ' + @permnm + ' TO ' + @usnm + ';'


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

CLOSE backupFiles
DEALLOCATE backupFiles


DROP TABLE #serverpermdummy

PRINT ' '
PRINT ' '
PRINT '==========================================================================================================='
PRINT ' Information By Database'
PRINT ' '
PRINT ' '

CREATE TABLE #DummyDBDesc
( RecID int IDENTITY NOT NULL,
ServerName varchar(128) NULL,
DBName varchar(100) NULL,
RecoveryModel varchar(10) NULL,
CompatibilityLevel varchar(30) NULL,
ReadWriteDesc varchar(10) NULL
)
CREATE TABLE #dummyDBRoles
( RoleName varchar(200),
UserName varchar(200),
UserType varchar(200)
)

CREATE TABLE #dummyrolelist
( RoleName varchar(200)
)

CREATE TABLE #dummyDBUsers
( UserName varchar(200),
UserType varchar(200)
)
INSERT INTO #DummyDBDesc
select CONVERT(varchar(128),@@servername) AS ServerName, CONVERT(varchar(100),name) as DBName, CONVERT(varchar(10),recovery_model_desc) as RecoveryModel, --database_id,
CASE compatibility_level
WHEN 80 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2000 *'
WHEN 90 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2005'
WHEN 100 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2008'
WHEN 105 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2008 R2'
WHEN 110 THEN CONVERT(varchar(4),compatibility_level) + ' - SQL 2012'
ELSE CONVERT(varchar(4),compatibility_level)
END AS CompatibilityLevel,
CASE is_read_only
WHEN 0 THEN CONVERT(varchar(10),'RW')
ELSE CONVERT(varchar(10),'R')
END as ReadWriteDesc
FROM sys.databases
WHERE name NOT IN('tempdb','master','msdb','model') and name NOT LIKE '%ReportServer%' AND state = 0
--AND name = 'MyDatabase'
ORDER BY name


DECLARE backupFiles CURSOR FOR
SELECT DBName, RecoveryModel, CompatibilityLevel, ReadWriteDesc FROM #DummyDBDesc ORDER BY DBName
OPEN backupFiles

DECLARE @dbn varchar(100)
DECLARE @rm varchar(10)
DECLARE @cl varchar(30)
DECLARE @rwd varchar(10)


-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @dbn, @rm, @cl, @rwd

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'Database Name : ' + @dbn
PRINT 'Recovery Model : ' + @rm
PRINT 'Compatibility Level: ' + @cl
PRINT 'Read/Write : ' + @rwd
PRINT ' '
PRINT ' '


/* ================================================================================================================================================================= */
/* Database User Information */

--Start with a clean table to load the values
TRUNCATE TABLE #dummyDBUsers

-- Get roles for this database and load into the temp table
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBUsers SELECT CONVERT(varchar(100),name) AS UserName, CONVERT(varchar(100),type_desc) as UserType FROM sys.database_principals WHERE (type = ''S'' OR type = ''U'' OR type = ''G'') AND is_fixed_role = 0 AND (name NOT IN (''guest'',''dbo'',''INFORMATION_SCHEMA'',''sys''))'
--PRINT @cmd
EXEC (@cmd)

--Get the length of the longest occurance of the columns
SELECT @unmax = ISNULL(MAX(len(UserName)),0) FROM #dummyDBUsers
SELECT @utmax = ISNULL(MAX(len(UserType)),0) FROM #dummyDBUsers

--Set some minimum values so column doesn't print short
if @unmax < 25 SET @unmax = 25
if @utmax < 25 SET @utmax = 25

--Set and print the column headings for the role information
SET @col1nm = 'UserName'
SET @col1len = @unmax
SET @col2nm = ''
SET @col2len = 5
SET @col3nm = 'UserType'
SET @col3len = @utmax
PRINT ' '
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm))
PRINT SPACE(10) + REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len)

DECLARE backupFiles2 CURSOR FOR
SELECT UserName, UserType FROM #dummyDBUsers ORDER BY UserName

OPEN backupFiles2

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles2 INTO @un, @ut

WHILE @@FETCH_STATUS = 0
BEGIN
--Set and print the row details for the role information
SET @col1nm = SUBSTRING(@un,1,@unmax)
SET @col3nm = SUBSTRING(@ut,1,@utmax)

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

FETCH NEXT FROM backupFiles2 INTO @un, @ut
END

CLOSE backupFiles2
DEALLOCATE backupFiles2

PRINT ' '
PRINT ' '

if @outputtype = 2 -- create the statements to assign a user to this database
BEGIN
TRUNCATE TABLE #dummyuserassign

SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyuserassign select DISTINCT
CASE members.type_desc
WHEN ''WINDOWS_USER''
THEN ''CREATE USER [''+ members.name + ''] FOR LOGIN [''+ members.name+ '']'' + '' WITH DEFAULT_SCHEMA=['' + members.default_schema_name + '']''
WHEN ''SQL_USER''
THEN ''CREATE USER [''+ members.name + ''] FOR LOGIN [''+ members.name+ '']'' + '' WITH DEFAULT_SCHEMA=['' + members.default_schema_name + '']''
END AS CreateUser
from sys.database_principals members
inner join sys.database_role_members drm
on members.principal_id = drm.member_principal_id
inner join sys.database_principals roles
on drm.role_principal_id = roles.principal_id
where members.name <> ''dbo''
ORDER BY CreateUser'
--PRINT @cmd
EXEC (@cmd)


if exists( SELECT * FROM #dummyuserassign)
BEGIN
PRINT 'USE ' + @dbn
PRINT 'GO'
PRINT ' '
END
DECLARE myCursorVariable3 CURSOR FOR
SELECT LineVal FROM #dummyuserassign ORDER BY RecID

OPEN myCursorVariable3

-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable3 INTO @lineval

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @lineval

FETCH NEXT FROM myCursorVariable3 INTO @lineval
END

CLOSE myCursorVariable3
DEALLOCATE myCursorVariable3
END



if @includeroleinfo = 1
BEGIN
/* ================================================================================================================================================================= */
/* Role Information */
SELECT @collationname = collation_name FROM master.sys.databases WHERE name = @dbn
if @collationname IS NULL
print 'null for ' + @dbn

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

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

--Start with a clean table to load the values
TRUNCATE TABLE #dummyDBRoles

-- Get roles for this database and load into the temp table
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBRoles select CONVERT(varchar(200),roles.name) AS RoleName, CONVERT(varchar(200),members.name) AS UserName, CONVERT(varchar(200),members.type_desc) AS UserType from sys.database_principals members inner join sys.database_role_members drm on members.principal_id = drm.member_principal_id inner join sys.database_principals roles on drm.role_principal_id = roles.principal_id where members.name <> ''dbo'' ORDER BY members.name, roles.name'
--PRINT @cmd
EXEC (@cmd)

-- Now add in any roles that are present in the database that do not have anyone assigned to them (those that are already in the temp table)
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBRoles SELECT CONVERT(varchar(200),name) AS RoleName, ''--none--'' As UserName, '''' AS UserType FROM sys.database_principals WHERE type = ''R'' and is_fixed_role = 0 and name <> ''public'' AND (name NOT IN (SELECT RoleName FROM #dummyDBRoles))'
--PRINT @cmd
EXEC (@cmd)

-- now get a list of database roles that were created and print them as CREATE ROLE statements
if @outputtype = 2
BEGIN
TRUNCATE TABLE #dummyrolelist
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyrolelist SELECT name FROM sys.database_principals WHERE type = ''R'' AND name <> ''public'' AND is_fixed_role = 0'
--PRINT @cmd
EXEC (@cmd)
PRINT 'USE ' + @dbn
PRINT 'GO'
PRINT ' '
DECLARE myCursorVariable4 CURSOR FOR
SELECT RoleName FROM #dummyrolelist

OPEN myCursorVariable4

-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable4 INTO @rn

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'CREATE ROLE ' + @rn
PRINT @cmd


FETCH NEXT FROM myCursorVariable4 INTO @rn
END

CLOSE myCursorVariable4
DEALLOCATE myCursorVariable4
END

--Get the length of the longest occurance of the columns
SELECT @rnmax = ISNULL(MAX(len(RoleName)),0) FROM #dummyDBRoles
SELECT @unmax = ISNULL(MAX(len(UserName)),0) FROM #dummyDBRoles
SELECT @utmax = ISNULL(MAX(len(UserType)),0) FROM #dummyDBRoles

--Set some minimum values so column doesn't print short
if @rnmax < 25 SET @rnmax = 25
if @unmax < 25 SET @unmax = 25
if @utmax < 25 SET @utmax = 25

--Set and print the column headings for the role information
SET @col1nm = 'RoleName'
SET @col1len = @rnmax
SET @col2nm = ''
SET @col2len = 5
SET @col3nm = 'UserName'
SET @col3len = @unmax
SET @col4nm = ''
SET @col4len = 5
SET @col5nm = 'UserType'
SET @col5len = @utmax
PRINT ' '
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm))
PRINT SPACE(10) + REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len) + SPACE(@col4len) + REPLICATE('=',@col5len)

-- Print the script to set the database context
if @outputtype = 2
BEGIN
PRINT 'USE ' + @dbn
PRINT 'GO'
PRINT ' '
END

--statement to get all roles for this database
--SELECT name FROM sys.database_principals WHERE type = 'R' and is_fixed_role = 0 and name <> 'public'
--can use to script the CREATE ROLE statements

-- Now loop through the roles
DECLARE backupFiles2 CURSOR FOR
SELECT RoleName, UserName, UserType FROM #dummyDBRoles ORDER BY RoleName

OPEN backupFiles2

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles2 INTO @rn, @un, @ut

WHILE @@FETCH_STATUS = 0
BEGIN
--Set and print the row details for the role information
SET @col1nm = SUBSTRING(@rn,1,@rnmax)
SET @col3nm = SUBSTRING(@un,1,@unmax)
SET @col5nm = SUBSTRING(@ut,1,@utmax)
if @outputtype = 1
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm))
if @outputtype = 2
BEGIN
if @col3nm <> '--none--'
PRINT 'exec sp_addrolemember [' + @col1nm + '], [' + @col3nm + '] --Usertype= ' + @col5nm
else
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len) + @col5nm + SPACE(@col5len-len(@col5nm))
END

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

CLOSE backupFiles2
DEALLOCATE backupFiles2

PRINT ' '
PRINT ' '
END


if @includeobjlvlperms = 1
BEGIN
/* ================================================================================================================================================================= */
/* Object-Level Permissions Information */
--Start with a clean table to load the values
TRUNCATE TABLE #dummyDBPerms

-- Get permissions for this database and load into the temp table
-- I'm sure some of this part came from elsewhere. My appologies to the originator.
SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBPerms '
SET @cmd = @cmd + 'select p.state_desc, p.permission_name, s.name, o.name, u.name, CASE o.type WHEN ''P'' THEN ''SPROC''
WHEN ''V'' THEN ''View''
WHEN ''U'' THEN ''Table''
WHEN ''FN'' THEN ''Function (scaler)''
WHEN ''TF'' THEN ''Function (table-valued)''
ELSE o.type_desc END AS ObjectType,
CONVERT(varchar(200),u.type_desc) AS UserType
from sys.database_permissions p
inner join sys.objects o on p.major_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
ORDER BY o.type, o.name collate Latin1_general_CI_AS, u.name collate Latin1_general_CI_AS'
--PRINT @cmd
EXEC (@cmd)

--Get the length of the longest occurance of each of the columns
SELECT @sdmax = ISNULL(MAX(len(StateDesc)),0) FROM #dummyDBPerms
SELECT @pnmax = ISNULL(MAX(len(PermName)),0) FROM #dummyDBPerms
SELECT @snmax = ISNULL(MAX(len(SchemaName)),0) FROM #dummyDBPerms
SELECT @onmax = ISNULL(MAX(len(ObjectName)),0) FROM #dummyDBPerms
SELECT @unmax = ISNULL(MAX(len(UserName)),0) FROM #dummyDBPerms
SELECT @pdmax = ISNULL(MAX(len(ObjectType)),0) FROM #dummyDBPerms
SELECT @utmax = ISNULL(MAX(len(UserType)),0) FROM #dummyDBPerms

--Set some minimum values so column doesn't print short
if @sdmax < 15 SET @sdmax = 15
if @pnmax < 15 SET @pnmax = 15
if @snmax < 10 SET @snmax = 10
if @onmax < 15 SET @onmax = 15
if @unmax < 15 SET @unmax = 15
if @pdmax < 15 SET @pdmax = 15 --ObjectType
if @utmax < 15 SET @utmax = 15 --UserType

--Set and print the column headings for the permissions information
SET @col1nm = 'StateDesc'
SET @col1len = @sdmax
SET @col2nm = ''
SET @col2len = 5
SET @col3nm = 'PermName'
SET @col3len = @pnmax
SET @col4nm = ''
SET @col4len = 5
SET @col5nm = 'Schema'
SET @col5len = @snmax
SET @col6nm = ''
SET @col6len = 5
SET @col7nm = 'Object'
SET @col7len = @onmax
SET @col8nm = ''
SET @col8len = 5
SET @col9nm = 'User'
SET @col9len = @unmax
SET @col10nm = ''
SET @col10len = 5
SET @col11nm = 'ObjectType'
SET @col11len = @pdmax
SET @col12nm = ''
SET @col12len = 5
SET @col13nm = 'UserType'
SET @col13len = @utmax

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

--Loop through the permissions for this database and format and print them
DECLARE backupFiles2 CURSOR FOR
SELECT StateDesc,PermName,SchemaName,ObjectName,UserName,ObjectType,UserType FROM #dummyDBPerms ORDER BY Schemaname,ObjectName,UserName

OPEN backupFiles2

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles2 INTO @sd, @pn, @sn, @on, @un, @pd, @ut

WHILE @@FETCH_STATUS = 0
BEGIN
--Set and print the row details for the permissions information
SET @col1nm = SUBSTRING(@sd,1,@sdmax)
SET @col3nm = SUBSTRING(@pn,1,@pnmax)
SET @col5nm = SUBSTRING(@sn,1,@snmax)
SET @col7nm = SUBSTRING(@on,1,@onmax)
SET @col9nm = SUBSTRING(@un,1,@unmax)
SET @col11nm = SUBSTRING(@pd,1,@pdmax)
SET @col13nm = SUBSTRING(@ut,1,@utmax)

--print the detail record for the permissions
if @outputtype = 1
PRINT SPACE(10) + @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nm + SPACE(@col3len-len(@col3nm)) + SPACE(@col4len)+ @col5nm + SPACE(@col5len-len(@col5nm)) + SPACE(@col6len)+ @col7nm + SPACE(@col7len-len(@col7nm)) + SPACE(@col8len) + @col9nm + SPACE(@col9len-len(@col9nm)) + SPACE(@col10len) + @col11nm + SPACE(@col11len-len(@col11nm)) + SPACE(@col12len) + @col13nm + SPACE(@col13len-len(@col13nm))
if @outputtype = 2
PRINT @col1nm + ' ' + @col3nm + ' ON [' + @col5nm + '].[' + @col7nm + '] TO [' + @col9nm + '] --ObjectType=' + @col11nm + ' UserType=' + @col13nm


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

CLOSE backupFiles2
DEALLOCATE backupFiles2

PRINT ' '
PRINT ' '

END


if @outputtype = 2 AND @includedefaultdb = 1
BEGIN
if EXISTS (SELECT name FROM master.sys.server_Principals WHERE type in ('G','S','U') AND default_database_name = @dbn)
BEGIN
PRINT ' '
PRINT ' '
PRINT '-- Here are the logins and their default database settings'
PRINT ' '

DECLARE myCursorVariable CURSOR FOR
SELECT name, default_database_name as DefaultDB
FROM master.sys.server_Principals
WHERE type in ('G','S','U') AND default_database_name = @dbn
ORDER BY name

OPEN myCursorVariable

-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ALTER LOGIN [' + @loginname + '] WITH DEFAULT_DATABASE = ' + @dbnametouse

FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse
END

CLOSE myCursorVariable
DEALLOCATE myCursorVariable

PRINT ' '
PRINT ' '
END
END




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

--Get the next database name and info to use in the database loop
FETCH NEXT FROM backupFiles INTO @dbn, @rm, @cl, @rwd
END

CLOSE backupFiles
DEALLOCATE backupFiles

/* =============================================================================================== */
--Dispose of the temporary tables
DROP TABLE #DummyDBDesc
DROP TABLE #dummyDBRoles
DROP TABLE #dummyDBUsers
DROP TABLE #dummyDBPerms
DROP TABLE #dummyuserassign
DROP TABLE #dummyrolelist

SET NOCOUNT OFF



Thank you, will check this method out.
Post #1462922
Posted Thursday, June 13, 2013 1:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 1,379, Visits: 2,697
I wouldn't worry too much about the DB logins and their permissions as these are stored in the user database itself.
The main thing is the instance level logins and SID's - These are kep in the master database
Post #1462923
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse