This will be my contribution to the T-SQL Tuesday Challenge that was originally created by Adam Machanic. This month, my good friend Allen White (Blog | Twitter) is hosting the challenge. The topic he has chosen is, “What T-SQL tricks do you use today to make your job easier?” I enjoy this topic and understand why he chose it. As our experience grows, we compose dozens if not hundreds of methods in order to get our job done quickly or simply. We’ve all been faced with challenges and found the trick to resolving them given our own unique installations of SQL Server. This is a great way to get a compilation of everyone’s tricks and see how we may share them with the community.
The orphaned user script can be used to resolve database user disconnections from SQL Server logins after a restore is performed. There are other applications for this script but the restore problem with user to logins is the one that will be focused on today.
The problem defined
A common misconception is that database users and logins are the same objects. With SQL Server, these are two unique security objects and two completely different levels of security. A database user can exist without a valid SQL login, and vice versa. Although these two objects can exist without each other, a database user is usually only effective if a SQL Login is tied to it. This is the path to connecting to the SQL Server instance itself and the database user is later evaluated internally to the database permissions the user has that is tied to the login.
In SQL Server 2012 and the AdventureWorks database, right click the SecurityàUsers nodes and click New User… Drop the list down and notice the “SQL user without login”. If this choice is utilized, there will be an orphaned database login, since there is no valid SQL login tied to it.
A common problem with this orphaned situation is when a database is restored. If a database is restored using a default strategy and no steps other than the restore command are taken, all the database users that were created in that restored database will also be restored. Since these database users did not have a login or the connection to the SQL login has been severed, there are steps that need to be taken to reattach these objects.
Remapping User to Login
To remap a database user to a SQL login, the system procedure SP_CHANGE_USERS_LOGIN is available. This procedure is on the list to be removed from future SQL Server releases and has been from SQL Server 2012 as of RC0. Instead, the ALTER USER command must be used. Since the procedure is still used in all the current versions of SQL Server, it will be shown as well as the replacement method of using ALTER USER.
To call SP_CHANGE_USERS_LOGIN, use the following syntax.
sp_change_users_login [ @Action = ] ‘action’
[ , [ @UserNamePattern = ] ‘user’ ]
[ , [ @LoginName = ] ‘login’ ]
[ , [ @Password = ] ‘password’ ]
The procedure is quite simple but to be effective on a database that was restored with hundreds of logins that may or may not even exist on the SQL Server instance the database was restored to, a script is required. If a script was not created, the task could potentially take hours to create and remap all the database users.
For SQL Server 2012, the ALTER USER is used by simply using dynamic T-SQL to generate the command and set the user name and password. The one feature that previous versions of this script lacked was the differentiation between SQL user logins and Windows logins. With Windows logins, the password is not set. When using the ALTER USER method, if a SQL Server login is not found, the CREATE LOGIN statement is called to create a login. There should be a check for the type of database user first though. Then the decision can be made on how to build the CREATE LOGIN statement. This enhancement is added to the SQL Server 2012 version.
The below script has a work flow of first identifying the orphaned database users by utilizing the same sp_change_users_login with the ‘report’ action called. This returns the database users of the database it is executed in and then allows the script to check the sys.server_principals for a valid login. If a login is not found, one is created with the same name as the database user. The password is set as a default password. This password should be immediately dealt with as a change method by the user or some other compliant method to prevent security problems. Once the login is created, the sp_change_users_login is used to remap the database user to the new SQL login or to an existing login that was found.
SQL Server 2005, 2008, 2008 R2 Versions
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
SET NOCOUNT ON USE AdventureWorks GO DECLARE @loop INT DECLARE @USER sysname IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL BEGIN DROP TABLE #orphaned END CREATE TABLE #Orphaned (UserName sysname, UserSID VARBINARY(85),IDENT INT IDENTITY(1,1)) INSERT INTO #Orphaned EXEC SP_CHANGE_USERS_LOGIN 'report'; IF(SELECT COUNT(*) FROM #Orphaned) > 0 BEGIN SET @loop = 1 WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned) BEGIN SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop) IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0 BEGIN EXEC SP_ADDLOGIN @USER,'password' END EXEC SP_CHANGE_USERS_LOGIN 'update_one',@USER,@USER PRINT @USER + ' link to DB user reset'; SET @loop = @loop + 1 END END SET NOCOUNT OFF
SQL Server 2005, 2008, 2008 R2 and 2012 Version with Windows Login check
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
SET NOCOUNT ON USE AdventureWorks GO DECLARE @loop INT DECLARE @USER sysname DECLARE @sqlcmd NVARCHAR(500) = '' IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL BEGIN DROP TABLE #orphaned END CREATE TABLE #Orphaned (UserName sysname,IDENT INT IDENTITY(1,1)) INSERT INTO #Orphaned (UserName) SELECT [name] FROM sys.database_principals WHERE [type] IN ('U','S') AND is_fixed_role = 0 AND [Name] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA') IF(SELECT COUNT(*) FROM #Orphaned) > 0 BEGIN SET @loop = 1 WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned) BEGIN SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop) IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0 BEGIN IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER') BEGIN SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] FROM WINDOWS' Exec(@sqlcmd) PRINT @sqlcmd END IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER') BEGIN SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] WITH PASSWORD = N''password''' Exec(@sqlcmd) PRINT @sqlcmd END END SET @sqlcmd = 'ALTER USER [' + @USER + '] WITH LOGIN = [' + @USER + ']' Exec(@sqlcmd) PRINT @USER + ' link to DB user reset'; SET @loop = @loop + 1 END END SET NOCOUNT OFF
To perform the below steps, you will need to use a domain account which is member member of Domain Admins and Enterprise Admins Groups and member of the Schema Admins group. I will be using the domain Administrator account which is already a member of all these groups.
Migrating your Domain Controller from Windows Server 2008 to Windows Server 2012 undergoes a few major steps, which are discussed in details below :
Step 1 : Check Forest and Domain Functional Level
The Forest Functional level and Domain Functional Level must be at least Windows Server 2003
To Check The Functional Levels, read my article : How to Raise the Forest and Domain Functional Levels in Windows Server 2008/R2
Step 2 : Preparing Active Directory Schema
The schema has to be upgraded and prepared for Windows Server 2012. To do this you have to run the adprep command.
Insert the Windows Server 2012 /R2 DVD into the DVD drive of the Windows Server 2008 / R2 Domain Controller
Open command prompt in administrative mode, and type adprep /forestprep and press enter.
Open the Directory of the DVD drive and navigate to adprep directory and then type : adprep /forestprep
You will receive a warning that all active directory domain controllers in the forest must be running at least windows server 2003. click c andEnter to confirm and continue.
Adprep will successfully update the forest
Then type adprep /domainprep and press enter
Step 3 : Install Windows Server 2012 and join it to the Windows Server 2008 Domain
For Installing Windows Server 2012, read this article : How To Install Windows Server 2012
For Joining Windows Server 2012 to the domain of Windows Server 2008, read this article : How To Join Windows Server 2012 to a Domain
Step 4 : Setting Up Additional Active Directory Domain Controller With Windows Server 2012
By now you already have your Windows Server 2008 Domain Controller, and you have installed Windows Server 2012 and is now a domain member. Now we need to introduce our first Windows Server 2012 domain controller in our network. This has been discussed in a previous article, read it here :Setting Up Additional Active Directory Domain Controller With Windows Server 2012
Step 5 : Transferring the Flexible Single Master Operations (FSMO) Role
You have your Windows Server 2008 Domain Controller ( in my lab its called : ELMAJDAL-DC ) and have an additional Windows Server 2012 domain controller ( in my lab its called : ELMAJDAL-DC13). To complete the migration , we need to transfer 5 FSMO roles to the new domain controller.
The five FSMO roles are:
Domain Naming Master
Relative ID (RID) Master
To check who is currently holding FSMO, run the following command : netdom query fsmo
The FSMO roles are currently with the Windows Server 2008 R2 domain controller : ELMAJDAL-DC
The FSMO roles are going to be transferred, using the following three MMC snap-ins :
Active Directory Schema snap-in : Will be used to transfer the Schema Master role
Active Directory Domains and Trusts snap-in : Will be used to transfer the Domain Naming Master role
Active Directory Users and Computers snap-in : Will be used to transfer the RID Master, PDC Emulator, and Infrastructure Master roles
Lets start transferring the FSMO roles.
Using Active Directory Schema snap-in to transfer the Schema Master role
You have to register schmmgmt.dll in order to be able to use the Active Directory Schema snap-in
Open Command Prompt in administrative mode and type regsvr32 schmmgmt.dll
Open Microsoft Management Console , mmc
Click File > then click Add/Remove Snap-in…
From the left side, under Available Snap-ins, click on Active Directory Schema, then click Add > and then click OK
Right click Active Directory Schema, then click Change Active Directory Domain Controller…
From the listed Domain Controllers, click on the domain controller that you want to be the schema master role holder and then click on OK
You will receive a message box stating that the schema snap-in is not connected to a schema operations master. That is for sure, as we have not yet set this Windows Server 2012 domain controller as a Schema Master role holder. This will be done in the next step. Click OK
In the console tree, right click Active Directory Schema [DomainController.DomainName], and then click Operations Master…
On the Change Schema Master page, the current schema master role holder will be displayed ( ex. ELMAJDAL-DC.ELMAJDAL.COM) and the targeted schema holder as well (ex. ELMAJDAL-DC13.ELMAJDAL.COM). Once you click Change, the schema master holder will become
ELMAJDAL-DC13.ELMAJDAL.COM , click Change
Click Yes to confirm the role transfer
The role will be transferred and a confirmation message will be displayed. Click OK
Then click Close, as you can see in the below snapshot, the current schema master is ELMAJDAL-DC13.ELMAJDAL.COM which is the Windows Server 2012 DC
Using Active Directory Domains and Trusts snap-in to transfer the Domain Naming Master Role
From the Start Screen, open the Active Directory Domains and Trusts
Right click Active Directory Domains and Trusts, then click Change Active Directory Domain Controller…
From the listed Domain Controllers, click on the domain controller that you want to be the Domain Naming master role holder and then click onOK
Right click Active Directory Domains and Trusts, then click Operations Master…
On the Operations Master page, we are going to change the Domain Naming role holder from ELMAJDAL-DC.ELMAJDAL.COM toELMAJDAL-DC13.ELMAJDAL.COM, Click Change
Click YES to confirm the transfer of the Domain Naming role. The role will be transferred and a confirmation message will be displayed. ClickOK , then click Close
Using Active Directory Users and Computers snap-in to transfer the RID Master, PDC Emulator, and Infrastructure Master Roles
From the Start Screen, open the Active Directory Users and Computers console
Right click your domain and select Operations Masters
In the Operations Masters window, ensure the RID tab is selected.
Click the Change button. Select Yes when asked about transferring the operations master role. Once the operations master role has successfully transferred, click OK to continue.
Ensure the Operations Master box now shows your new Windows Server 2012
Repeat steps 4 to 6 for the PDC and Infrastructure tabs.
Once completed, click Close to close the Operations Masters window.
If you would like to check if the roles were properly transferred, open command prompt and type: netdom query fsmo. If you see your new server listed in each role, you have successfully transferred all of your FSMO roles to the new server
**** rimuovere \\ da filtro isapi ****
I was investigating a fault today for a client – their new SBS 2011 server had stopped working with WSUS. As my investigation went on I found that this issue would also affect the SBS Client Connect as well as other web based applications on this server.
The SBS console was reporting “An error occured while retrieving updates information.” as the screen below shows.
I did some digging and found that the WSUS console was broken too.
The event logs showed the error below.
Event ID: 2274
ISAPI Filter ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\\aspnet_filter.dll’ could not be loaded due to a configuration problem. The current configuration only supports loading images built for a AMD64 processor architecture. The data field contains the error number. To learn more about this issue, including how to troubleshooting this kind of processor architecture mismatch error, see http://go.microsoft.com/fwlink/?LinkId=29349.
Look closely and you will notice above that there’s a double \\ in the path name.
Once I’ve modified it to remove the double \\ and done an IISRESEt – it’s all working just fine. The picture below shows the problem line AFTER I’ve modified it.
If you facing network connection issue, or more accurately unable to access or connect to Internet or network problem in Windows operating system such as Windows 2003, Windows XP, Windows Vista, Windows 7, Windows 8, Windows 8.1, Windows 10, Windows Server and etc, you can try to reinstall and reset TCP/IP stack or Internet Protocol, one of the core component of the operating system, which cannot be uninstalled.
Again, with a corrupt TCP/IP stack, the same woes may happen – unable to establish a connection to the server, unable to load a web page, unable to browse and surf the Internet, even though network connection to broadband router or wireless router appear to be OK.
When all means run out, try to reinstall the IP stack with NetShell utility. NetShell utility (netsh) is a command-line scripting interface for the configuring and monitoring of Windows networking service.
To reinstall and reset the TCP/IP stack (Internet Protocol) to its original state as same as when the operating system was first installed, simply use the following command in a Command Prompt (Cmd) shell. In Windows Vista or newer, open an elevated Command Prompt with Administrator privileges instead. A log file name must be specified where actions taken by netsh will be recorded on newly created or appended if already existed file..
netsh int ip reset [ log_file_name ]
netsh int ip reset c:\resetlog.txt
Restart the computer once the command completed.
The command will remove all user configured settings on TCP/IP stack and return it to original default state by rewriting pertinent registry keys that are used by the Internet Protocol (TCP/IP) stack to achieve the same result as the removal and the reinstallation of the protocol. The registry keys affected are:
For Windows Vista, Windows XP, Windows Server 2008 or Windows Server 2003: MicrosoftFixit50199.msi
Quando si utilizza Windows Server Backup si potrebbe incorrere in questo errore:
Gestione filtri: collegamento al volume ‘\Device\Harddisk10\DR28’ non riuscito. È necessario riavviare il sistema affinché il volume sia disponibile per i filtri. Stato finale: 0xc03a001c.