I lost my administrative privileges in SQL Server. What about now?

As a result of some failure or operational error, sometimes we may encounter a user who lost administrative privileges in SQL Server. To make matters worse, often this was the only user with administrative privileges on the database. And now, how to solve it?

Fortunately, there is a way to reactivate the administrator privilege to this user, since this user is also a Windows administrator user. Confirmed this hypothesis, let’s go to the steps to re-enable the administrator privileges for this user in SQL Server:

1. Log on to Windows with the user who lost access to SQL Server. Keep in mind that this user must have administrator privileges on Windows.


2. Restart the service in SQL Server by adding the “-mSQLCMD” parameter, using the SQL Server Configuration Manager. This parameter allows access to SQL Server only through the SQLCMD:
   
   
   
   
   
3. Connect to SQL Server through the SQLCMD:
   
   


4. Add the user login to the role “sysadmin”, through the following command:
   
   
   ALTER SERVER ROLE [sysadmin] ADD MEMBER [<domain>\<login>];
   GO
   
   
5. Remove the parameter “-mSQLCMD” of the SQL Server service startup.


6. Restart the SQL Server service.

For more information, see the articles below:

• Connect to SQL Server When System Administrators Are Locked Out: https://msdn.microsoft.com/en-us/library/dd207004.aspx
• ALTER SERVER ROLE (Transact-SQL): https://msdn.microsoft.com/en-us/library/ee677634.aspx

Errors 8623 or 8632: how to workaround them?

After executing a query with many values ​​inside of an “IN” clause in a SQL Server database, we can receive the errors 8623 or 8632, described below:

Error 8623:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. 

Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

These errors can occur when an extremely large number of values are used ​​inside of an “IN” clause. This may consume excessive resources by the server, and generate performance problems, causing one of the errors above.

However, we must pay attention to deal with cases like this because often these values ​​can be written automatically by an application, which can make the diagnosis of the problem harder.

To workaround this problem, we must rewrite the query, reducing the number of values ​​inside of the “IN” clause. Or, if we need to use several values ​​inside of an “IN” clause, we must replace them with values ​​in a table. This will minimize the performance impact on the server, avoiding the errors above.

More information in the article below:

• IN (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms177682.aspx