Microsoft SQL Server Error 262 Solutions – cannot create database

Sql Server error 262 is common issue faced by SQ Server users when trying to create new database in SQL Server Management Studio. This error can appear also during login on SSMS. A message is returned that states that the account does not have “sysadmin” role to SQL Server.

Another reason si that starting with SQL 2008, local Administrators group is no longer added by default during SQL Server setup. You even have to use a manual step to add the current user as a local administrator. This means that it is possible, especially if you don’t use mixed authentication (or have forgotten the sa password), that you can be completely locked out of your own SQL Server instance.

For any reason you don’t have any account available on SQL Server with “sysadmin” role. So you can’t edit other users to add the specific database on their “user mapping”. The following article will tech how to fix those issue with privileges and permissions.

Error:

CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)

SQL Server error 262

SQL Server error 262

 

SQL server error 262

Case 1 – You have a login available account with “sysadmin” role.

This case assumes that you have an account with sysadmin roles on SQL Server like an Admin. During the setup of SQL Server you must have specified (at least one) system administrator for the instance of SQL Server, or SA account if you are using mixed mode authentication. You need to use credentials for that account to gain access to your SQL Server.

After accessing the SQL Server Management Studio with this account you can create or edit other users (The user that having the error in his case) in order to have sysadmin role.

  1. Log on to the computer using the credentials for the Administrator account.
  2. Click the Start button, point to All Programs, click Microsoft SQL Server, and then click SQL Server Management Studio.
  3. Connect to the instance of SQL Server.
  4. Click Security, right-click Logins, and then click New Login.
  5. In the Login name box, enter the user name.
  6. In the Select a page , click Server Roles, select the sysadmin check box, and then click OK.

 

Case 2 – How to gain sysadmin access on SQL Server when you don’t have any user with “sysadmin” role.

The worst case is when you don’t have any sysadmin user to login on the system. The single option is to start SQL server in a single user mode, in that case anyone who is local administrator on that computer can connect to SQL Server as a member of sysadmin fixed server role.

READ ALSO -   Microsoft sql server error 53 – Connection Issue with SQL Server Instance

Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer’s local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

Make sure you stop SQL agent service, before you put SQL server service in Single user mode else you can error saying only one user can be connected.

 

  1. Stop the SQL Server Service:
    • Use SQL Server Configuration manager and stop the SQL service or use services to locate and stop SQL Service.
  2. Start SQL Server in Single User mode
    • Use start-up parameter m to start SQL Service in single user mode.
      • “net start MSSQLServer /m SQLCMD” if you want to use command line
      • Using configuration Manager – Locate the service which you have stopped earlier. Go to its properties, “Advanced”, click on drop down at “Startup Parameters” and add ;-m
  3. Connect to SQL Server and add desired account in SysAdmin role.
    • Open administrator command prompt. (i.e. right click on command prompt shortcut and choose “Run As Administrator”
    • Type sqlcmd –S For example
      • sqlcmd –S. (for default instance)
      • sqlcmd –S.\MyInstance
    • You are connected as System Administrator, because you are part of local administrator group in windows.
    • Use this script I normally use to add local administrator group as a part of SysAdmin group in SQL Server Instance.

USE [master]

GO

CREATE LOGIN [Domain\Username] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

EXEC master..sp_addsrvrolemember @loginame = N’ Domain\Username, @rolename = N’sysadmin’

GO

  1. Stop SQL Server Service.
  2. Remove startup parameter and Start SQL Service normally

This procedure is described on MSDN

Read also other SQL Server Helping Posts:

  1. Find Location of SQL Server Error Log
  2. SQL Server Error 300
  3. Restore Master Database

Case 3 – Adding specific permissions to the user

Grant SHOWPLAN access to the SQL user

SQL Server Error 262 can appear when user tries to check the execution plan using CTRL + M. A calculated execution plan is a SQL Server query plan that is created without actually running the query plan. Its main purpose is to check how query will behave without having to execute it. In order to include the execution plan in the query, we can allow user to grant SHOWPLAN access to the user. Fixing the error is possible by providing particular user the SHOWPLAN access using following commands:

USE DatabaseName

GO

GRANT SHOWPLAN TO UserName

GO

We can run the commands in the database where user faces the issue to make them view the query execution plan without any complications.

 

Let me know if you have any issue with the solutions regarding the Sql Server error 262.

Related Articles