Jan 22

Grant execute permissions to ALL stored procedures in a database

I’ve found many scenarios where there are certain users or groups that require execute permissions for all stored procedures in a database. Rather then granting access to all the objects individually, not to mention new objects that are created after you’ve granted access, I prefer to create an executor role and assign their user account to the role. The following works for SQL 2005 and above:

Then grant the db_executor role to their user account. Done!

Dec 06

Start SQL in Single User Mode

Occasionally you’ll find a situation where you need to start SQL server in single user mode. It might be to elevate user privileges or change the SA password to an instance you don’t have access to. You could also recover a corrupt master or system database this way. Whatever the case may be, here is the procedure to start SQL in single user mode (“Run as Administrator” should be selected for all command below) :

The following example will be for a DEFAULT instance. If you are using a named instance change:

MSSQLSERVER to “SQL Server (instance)”

  1. Stop the Instance

  2. Start SQL in Single User Mode

  3. Enter SQL via Command Line

     or for a named instance:

     

Once connected you can run t-SQL commands. Example: check the version of SQL server:

 

If you run into a “Login Failed” or “Permission Denied” error continue on to this post: Login Failed or Permission Denied when Starting SQL in Single User Mode