If you forget to add the administrator group (or at least the current user) to admin groups, and do not know the SA password then you have only two options
- reinstall SQL server
- use single-user-mode to add the accounts
Ok now the single-user-mode procedure.
0) login with Administrator (exactly the Administrator user, with another administrator account there will be problems)
1) open services.msc
2) go the "SQL Server (xxx)" service and stop it
3) open "Sql server configuration manager" from the start menu and make sure that the protocols "Shared memory", "Named pipes", "TCP/IP" are enabled
4) Go back to the services console right click "SQL Server (xxx)" and select properties and then enter "-m" as parameter. Click start
5) Now the server is started in single-user mode
6) Open command prompt and execute following command "sqlcmd -S
sqlcmd -S MYMACHINE\SQLEXPRESS -E
7) Execute following commands for each windows account that you want to add
CREATE LOGIN [MYMACHINE\Administrator] FROM WINDOWS
GO
exec sp_addsrvrolemember @loginame='MYMACHINE\Administrator', @rolename = 'sysadmin'
GO
8) Stop the SQL service and restart it without -m parameter.
9) Now you are able to log-in with Administrator user and you do have administrator rights on SQL server
No comments:
Post a Comment