Menu
I installed SQL Server 2008 R2 to my local machine. But, I can't create a new database because of rights (or lack of).
"CREATE DATABASE PERMISSION DENIED"
So, I tried to assign the admin privileges to my current login
"User does not have permission to perform this action."
I also tried to create a new login that would have admin privileges but with no luck. How do I grant myself admin rights so that I can create a database? I can re-install, but I prefer not to.
377
If you are a member of the local administrators group on the server, you can gain access to SQL Server. SQL Server has an emergency back door that you can access by restarting it in single-user mode. When in single-user, you can then add yourself as a login and add the login to the sysadmin group.
Here, you will learn to grant permissions to a user in SQL Server. You can GRANT and REVOKE permissions on various database objects in SQL Server. User permissions are at the database level. You can grant any or a combination of the following types of permissions: Select: Grants user the ability to perform Select operations on the table.
How can you grant access to a user for all databases on a SQL Server instance? You have a few different options, in SQL Server Management Studio, you can tick each checkbox for all databases from the user mapping interface in the login properties to grant the access. This can take a long time to finish due to the large number of databases.
In the Registered Servers area, right-click your server, and then click SQL Server Configuration Manager. This should ask for permission to run as administrator, and then open the Configuration Manager program. Close Management Studio. In SQL Server Configuration Manager, in the left pane, select SQL Server Services.
In this situation you usually won't have admin rights to the SQL Server 2008 Express instance, since by default only the person installing SQL Server 2008 is granted administrative privileges. The user who installed SQL Server 2008 Express can use SQL Server Management Studio to grant the necessary privileges to you.
Open a command prompt window. If you have a default instance of SQL Server already running, run the following command on the command prompt to stop the SQL Server service:
net stop mssqlserver Now go to the directory where SQL server is installed. The directory can for instance be one of these:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn Figure out your MSSQL directory and CD into it as such:
CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn Now run the following command to start SQL Server in single user mode. As SQLCMD is being specified, only one SQLCMD connection can be made (from another command prompt window).
sqlservr -m"SQLCMD" Now, open another command prompt window as the same user as the one that started SQL Server in single user mode above, and in it, run:
sqlcmd And press enter. Now you can execute SQL statements against the SQL Server instance running in single user mode:
create login [<<DOMAIN\USERNAME>>] from windows; -- For older versions of SQL Server: EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin'; -- For newer versions of SQL Server: ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>]; GO Source.
UPDATED Do not forget a semicolon after ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>]; and do not add extra semicolon after GO or the command never executes.
136
Yes - it appears you forgot to add yourself to the sysadmin role when installing SQL Server. If you are a local administrator on your machine, this blog post can help you use SQLCMD to get your account into the SQL Server sysadmin group without having to reinstall. It's a bit of a security hole in SQL Server, if you ask me, but it'll help you out in this case.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
