InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 00:52:59 GMT20http://www.sqlservercentral.com/Forums/Topic1411132-1526-1.aspxNote that if you move the hashed_passwords over, you're using the old SQL 2005 (0x0100) type password hashes SHA1(Binary(UCS2(Password+Salt))), not the newer, very slightly better SQL 2012 (0x0200) type password hashes SHA512(Binary(UCS2(Password+Salt))).Additionally, if you put the plaintext password in, you'll get a different salt, and the hash will therefore be different between Instance A and Instance B (regardless of version). If you use the hashed_password, any attacker seeing the hashes knows that cracking the one also cracks the other.Mon, 25 Feb 2013 13:59:16 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic1411132-1526-1.aspx[quote][b]Rade_ (2/21/2013)[/b][hr]This will extract only users from selected database. Got it here at sqlservercentral, can't remember who though.[code="sql"]USE DATABASEGOselect 'CREATE LOGIN ' + sl.name + ' WITH PASSWORD = ' + sys.fn_varbintohexstr(sl.password_hash) + ' HASHED, SID = ' + sys.fn_varbintohexstr(sl.sid) + ', DEFAULT_DATABASE = ' + quotename(sl.default_database_name) + ', DEFAULT_LANGUAGE = ' + sl.default_language_name + ', CHECK_EXPIRATION = ' + case when sl.is_expiration_checked = 0 then 'off' else 'on' end + ', CHECK_POLICY = ' + case when sl.is_policy_checked = 0 then 'off' else 'on' endfrom sys.sql_logins slwhere exists (select sid from sys.database_principals dp where dp.sid = sl.sid) and sl.principal_id > 4UNION ALLselect 'CREATE LOGIN ' + QUOTENAME(sp.name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + quotename(sp.default_database_name) + ', DEFAULT_LANGUAGE = ' + sp.default_language_namefrom sys.server_principals spwhere exists (select sid from sys.database_principals dp where dp.sid = sp.sid) AND sp.principal_id > 4 AND sp.type IN ('G','U')[/code][/quote]This looks like mine ;-)Fri, 22 Feb 2013 05:13:42 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1411132-1526-1.aspxThis will extract only users from selected database. Got it here at sqlservercentral, can't remember who though.[code="sql"]USE DATABASEGOselect 'CREATE LOGIN ' + sl.name + ' WITH PASSWORD = ' + sys.fn_varbintohexstr(sl.password_hash) + ' HASHED, SID = ' + sys.fn_varbintohexstr(sl.sid) + ', DEFAULT_DATABASE = ' + quotename(sl.default_database_name) + ', DEFAULT_LANGUAGE = ' + sl.default_language_name + ', CHECK_EXPIRATION = ' + case when sl.is_expiration_checked = 0 then 'off' else 'on' end + ', CHECK_POLICY = ' + case when sl.is_policy_checked = 0 then 'off' else 'on' endfrom sys.sql_logins slwhere exists (select sid from sys.database_principals dp where dp.sid = sl.sid) and sl.principal_id > 4UNION ALLselect 'CREATE LOGIN ' + QUOTENAME(sp.name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + quotename(sp.default_database_name) + ', DEFAULT_LANGUAGE = ' + sp.default_language_namefrom sys.server_principals spwhere exists (select sid from sys.database_principals dp where dp.sid = sp.sid) AND sp.principal_id > 4 AND sp.type IN ('G','U')[/code]Thu, 21 Feb 2013 14:23:03 GMTRade_http://www.sqlservercentral.com/Forums/Topic1411132-1526-1.aspxThank you all!What I need is only the logins needs to access to the database to be moved. I have no intention to move all logins, which are not related to the database to be moved.Any more idea?Many thanks once again.Wed, 20 Feb 2013 15:23:08 GMTssnrobtcokhttp://www.sqlservercentral.com/Forums/Topic1411132-1526-1.aspxThanks, this is excellentThu, 31 Jan 2013 12:55:52 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1411132-1526-1.aspxI think this may be what you are looking for. It is a script I wrote to do Instance Security Audit documentation. This is the latest version. Set the @outputtype variable to 1 for columnar (report style) listings, and 2 for the actual assignment statements.Hope it helps./* ====================================================================================================================== *//* = Instance Security Audit Documentation = *//* ====================================================================================================================== *//* Created Date: 12/28/2011 By: VikingDBA Modifications: 08/07/2012 Changed to also do Server Level Permissions (like VIEW ANY DATABASE, or VIEW SERVER STATE) 01/09/2013 Changed to print the scripts to create the CREATE ROLE and CREATE USER statements 01/31/2013 Changed to print the server level permission statements, and statements to set the default databases for users Dependencies: This script depends on the following to exist: none Summary: This script creates documentation for all databases in an instance, including the server level permissions, database role permissions, and individual database object permissions. Note that changing @outputtype = 1 sets for columnar (report style) and value of 2 creates the assignment statements NOTE: Runs for all databases, unless a specific database is used in the WHERE clause below. Also, see User Settable Variables section below to see if any variables need to be set.*/Use MASTERSET NOCOUNT ON/* ==================================================================================================================== */-- Security Audit for SERVER RolesDECLARE @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 intDECLARE @col2len intDECLARE @col3len intDECLARE @col4len intDECLARE @col5len intDECLARE @col6len intDECLARE @col7len intDECLARE @col8len intDECLARE @col9len intDECLARE @col10len intDECLARE @col11len intDECLARE @col12len intDECLARE @col13len intDECLARE @col14len intDECLARE @col15len intDECLARE @col16len intDECLARE @col17len intDECLARE @col18len intDECLARE @col19len intDECLARE @col20len intDECLARE @col1max intDECLARE @col2max intDECLARE @col3max intDECLARE @col4max intDECLARE @col5max intDECLARE @col6max intDECLARE @col7max intDECLARE @col8max intDECLARE @col9max intDECLARE @col10max intDECLARE @col11max intDECLARE @col12max intDECLARE @col13max intDECLARE @col14max intDECLARE @col15max intDECLARE @col16max intDECLARE @col17max intDECLARE @col18max intDECLARE @col19max intDECLARE @col20max intDECLARE @col1min intDECLARE @col2min intDECLARE @col3min intDECLARE @col4min intDECLARE @col5min intDECLARE @col6min intDECLARE @col7min intDECLARE @col8min intDECLARE @col9min intDECLARE @col10min intDECLARE @col11min intDECLARE @col12min intDECLARE @col13min intDECLARE @col14min intDECLARE @col15min intDECLARE @col16min intDECLARE @col17min intDECLARE @col18min intDECLARE @col19min intDECLARE @col20min intDECLARE @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 intDECLARE @pnmax intDECLARE @snmax intDECLARE @onmax intDECLARE @pdmax intDECLARE @unmax intDECLARE @rnmax intDECLARE @utmax intDECLARE @outputtype intDECLARE @prodlevel varchar(25)DECLARE @version varchar(250)DEClARE @prodver varchar(50)DECLARE @edition varchar(50)DECLARE @includeobjlvlperms bitDECLARE @includeroleinfo bitDECLARE @includedefaultdb bitDECLARE @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 VariablesSET @outputtype = 1 -- 1=columnar 2=assignment statementsSET @includeobjlvlperms = 1SET @includeroleinfo = 1SET @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 lineDECLARE @lvaltouse varchar(2000)DECLARE @lvallength intDECLARE @lvalct intDECLARE @spotcat intDECLARE @spotcatval intDECLARE @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 = @versionSET @lvallength = LEN(@lvaltouse)SET @lvalct = 1SET @spotcat = 1SET @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: ' + @lval1PRINT ' ' + @lval4PRINT '============================================================================================================='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 AuditINSERT INTO #rolememberdummyEXEC sp_helpsrvrolemember 'sysadmin'INSERT INTO #rolememberdummyEXEC sp_helpsrvrolemember 'securityadmin'INSERT INTO #rolememberdummyEXEC sp_helpsrvrolemember 'serveradmin'INSERT INTO #rolememberdummyEXEC sp_helpsrvrolemember 'dbcreator'INSERT INTO #rolememberdummyEXEC sp_helpsrvrolemember 'diskadmin'INSERT INTO #rolememberdummyEXEC sp_helpsrvrolemember 'processadmin'INSERT INTO #rolememberdummyEXEC sp_helpsrvrolemember 'setupadmin'INSERT INTO #rolememberdummyEXEC sp_helpsrvrolemember 'bulkadmin'SET @col1nm = 'Role'SET @col1len = 20SET @col2nm = ''SET @col2len = 8SET @col3nm = 'Member Name'SET @col3len = 30PRINT @col1nm + SPACE(@col1len-len(@col1nm)) + SPACE(@col2len) + @col3nmPRINT REPLICATE('=',@col1len) + SPACE(@col2len) + REPLICATE('=',@col3len)--SELECT CONVERT(varchar(30),ServerRole) as ServerRole, CONVERT(varchar(30),MemberName) AS MemberName FROM #rolememberdummyDECLARE backupFiles CURSOR FOR SELECT ServerRole, MemberName FROM #rolememberdummyOPEN backupFiles -- Loop through all the files for the database FETCH NEXT FROM backupFiles INTO @sr, @mnWHILE @@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, @mnEND CLOSE backupFiles DEALLOCATE backupFiles DROP TABLE #rolememberdummyPRINT ' '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 #serverpermdummySELECT l.name as UserName, l.type_desc AS UserType, p.state_desc AS StateDesc, p.permission_name AS PermNameFROM 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_principalsSET @col1nm = 'User Name'SET @col1len = 20SET @col2nm = ''SET @col2len = 8SET @col3nm = 'User Type'SET @col3len = 20SET @col4nm = ''SET @col4len = 8SET @col5nm = 'State Desc'SET @col5len = 20SET @col6nm = ''SET @col6len = 8SET @col7nm = 'Permission'SET @col7len = 30SET @col1min = LEN(@col1nm)SET @col3min = LEN(@col3nm)SET @col5min = LEN(@col5nm)SET @col7min = LEN(@col7nm)--Get the length of the longest occurance of the columnsSELECT @col1max = ISNULL(MAX(len(LTRIM(RTRIM(UserName)))),0) FROM #serverpermdummySELECT @col3max = ISNULL(MAX(len(LTRIM(RTRIM(UserType)))),0) FROM #serverpermdummySELECT @col5max = ISNULL(MAX(len(LTRIM(RTRIM(StateDesc)))),0) FROM #serverpermdummySELECT @col7max = ISNULL(MAX(len(LTRIM(RTRIM(PermName)))),0) FROM #serverpermdummy--Set some minimum values so column doesn't print shortif @col1max < @col1min SET @col1len = @col1min else SET @col1len = @col1maxif @col3max < @col3min SET @col3len = @col3min else SET @col3len = @col3maxif @col5max < @col5min SET @col5len = @col5min else SET @col5len = @col5maxif @col7max < @col7min SET @col7len = @col7min else SET @col7len = @col7maxif @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) ENDelse 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 #serverpermdummyOPEN backupFiles -- Loop through all the files for the database FETCH NEXT FROM backupFiles INTO @usnm, @ustp, @stdsc, @permnmWHILE @@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, @permnmEND CLOSE backupFiles DEALLOCATE backupFiles DROP TABLE #serverpermdummyPRINT ' '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 #DummyDBDescselect CONVERT(varchar(128),@@servername) AS ServerName, CONVERT(varchar(100),name) as DBName, CONVERT(varchar(10),recovery_model_desc) as RecoveryModel, --database_id, CASE compatibility_levelWHEN 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_onlyWHEN 0 THEN CONVERT(varchar(10),'RW')ELSE CONVERT(varchar(10),'R')END as ReadWriteDescFROM sys.databasesWHERE name NOT IN('tempdb','master','msdb','model') and name NOT LIKE '%ReportServer%' AND state = 0--AND name = 'MyDatabase'ORDER BY nameDECLARE backupFiles CURSOR FOR SELECT DBName, RecoveryModel, CompatibilityLevel, ReadWriteDesc FROM #DummyDBDesc ORDER BY DBNameOPEN 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 tablesDROP TABLE #DummyDBDescDROP TABLE #dummyDBRolesDROP TABLE #dummyDBUsersDROP TABLE #dummyDBPermsDROP TABLE #dummyuserassignDROP TABLE #dummyrolelistSET NOCOUNT OFFThu, 31 Jan 2013 08:05:07 GMTvikingDBAhttp://www.sqlservercentral.com/Forums/Topic1411132-1526-1.aspxI've had no problems with the sp_help_revlogin procedureWhat I need is a script that will script out everything in the sp_help_revlogin procedure and include server level permissions as well as database mappingsThanksTue, 29 Jan 2013 23:34:18 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1411132-1526-1.aspxI have done 3 SQL Server migrations from obsolete hardware to new over the past 6 months and have used sp_help_revlogin with little trouble to move the users and permissions over. (knock on wood). The only problem we had was some strange permissions stuff burried in msdb for some sharepoint logins, that I'm struggling to remember the details for.The targets were always brand new clean installations.What sort of trouble did you run into previously? (I'll search my notes and see what the details of the SharePoint service migration piece was)Tue, 29 Jan 2013 19:22:12 GMTjchapmanhttp://www.sqlservercentral.com/Forums/Topic1411132-1526-1.aspxHi AllIs there any way to script out all the Logins on a SQL Server instance, including server level privileges, mapped databases and permissions on the databases?I have used sp_help_revlogin from Microsoft on many occasions but that doesn't do everything I need.ThanksThu, 24 Jan 2013 07:25:44 GMTSQLSACT