If you’re doing a system copy of a SAP System on top of Microsoft SQL Server 2008 R2 and you’re using a local admin other than Administrator, then you need to continue to read. Otherwise you can skip this blog and read other valuable blogs.
When you try to logon to the target system to restore a SQL Database Backup to restore, and you’re not .\Administrator, probably you’ll get the following error:
Login failed for user ‘Domain\User’. (Microsoft SQL Server, Error: 18456)
1. Open an elevated Command Prompt.
2. Stop the SQL service with command: net stop mssqlserver
3. Start SQL Server in Single User Mode with command: “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr” -m”SQLCMD”
4. Now open another command prompt window and write command SQLCMD. In case of connecting to named instance we need to supply server and instance name as with -S switch:
5. Grand your admin user to SQL role sysadmin with following commands:
> EXEC master..sp_addsrvrolemember @loginame = N’STFANET\c2eadm’, @rolename = N’sysadmin’
6. Go back to first command window and press Ctrl+C to stop the SQL server and then type ‘Y’ for confirmation. This will stop the sql server.
Start SQL server again and no startup parameters need to specified this time.