How grant full privileges to new Windows 10 adminstrator in SQL Server
Use WIndows Authentication to access SQL Server 2014. Created new Windows 10 admin account and deleted old WIndows 10 admin account. The new WIndows 10 admin account can successfully log into SQL Server 2014, but has privileges to do nothing. Don't know sa password, so how can I configure the new Windows 10 admin account to have sa like privileges in SQL server 2014?
You can connect to SSMS with DAC connection and change sa password. After that you can login to SSMS as sa normally (with object explorer available) and then grant whatever permissions to the admin user in question. Please note that there is no way to connect to server via File -> Connect Object Explorer. This is by design, the explorer uses multiple connections, so DAC is not possible. 1. Please open SSMS and dismiss the initial connection dialog 2. Click the "Database Engine Query" icon located to the right of New Query in the toolbar 3. When the dialog opens, use the **ADMIN:your\_instance\_name** instead of just the instance name 4. Once the connection is established (please note that there is nothing shown in the object explorer, but this is OK 5. Change the sa password by executing the following query: exec sp_password null, 'your_password_goes_here', 'sa'; 6. Close DAC connection and now you can connect normally as **sa** using the password you just set in the previous step 7. Grant whatever permissions you need to the admin user in question Hope this helps. Oleg