I installed SQL Server 2008 Enterprise Edition on Windows 7 Ultimate from sadegh user.
This account exists in administrators role. after a few days I removed sadegh user from Windows and now I am using administrator user. But I can't login to SQL Server database engine using Windows authentication method and I receive this error message:
TITLE: Connect to Server
Cannot connect to SADEGH-PC.
ADDITIONAL INFORMATION:
Login failed for user 'Sadegh-PC\Administrator'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
please help me! thanks
You should need to enable SQL Server login user or enable SQL Server authentication mode after connecting to server with Windows authentication mode. Situation 6: The login is valid but server access is failed. Probably you have no admin privileges to server, or Windows Firewall has not allowed SQL Server access.
Login failed for user '<user_name>'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) Login failed for user '<user_name>'. (Microsoft SQL Server, Error: 18456) Cannot open user default database.
It occurs when a SQL server login logs in with the name that doesn’t exist in sql server. This error mostly comes in when users specify wrong user name or misspell the login name. So first thing you should check:
Local administrators are not given login privileges by default in SQL 2008. Only the user performing the install is defaulted sa privileges. During install you have the option to add additional users, but you apparently did not.
Depending on what mode SQL Server was installed (Windows Authentication or Mixed Mode), you may have to do a few things. If you installed in mixed mode, you can log in as sa and add the administrator user as a Login under the Security section. Make sure you assign sysadmin role to the user, which can be found under Server Roles.
If Windows Authentication mode was chosen during install then there are a couple of things you can do, but I think the easiest is just to recreate the user sadegh, login into SQL Server as that user, and follow the previous step to add the administrator user as a login.
UPDATE: If all else fails, you can run SQL Server in Single User Mode, which allows a single connection to the server, and enable/change the sa password. To do this you can:
EDIT:
As indicated by @jimasp in the comments, for step 6 you may have to do ALTER LOGIN sa WITH PASSWORD = '' UNLOCK;, because the sa account may be locked from too many login attempts.
Tested on Windows 7 Ultimate with SQL Server 2008 Standard. Hope this helps.
In previous versions of SQL the BUILTIN\Administrators group was given the sysadmin role. Local administrators are not given login privileges by default in SQL 2008. Only the user performing the install is defaulted sa privileges. During install you have the option to add additional users, but you apparently did not.
You will need to login as sa and configure whatever windows logins are required (such as the local administrator account).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With