CODEWITHSUNDEEP
c#sql-server
BBoone
BBoone

Reputation: 51

SQL Server : CREATE DATABASE permission denied in database 'master'

I am getting this error:

CREATE DATABASE permission denied in database 'master'.

An attempt to attach an auto-named database for file C:\APP_DATA\WRESTLING.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

but I give my service the administrator account:

enter image description here

So, why is it being denied?

Here is my code:

void ConnectToDb()
{
        connStringBuilder = new SqlConnectionStringBuilder();
        connStringBuilder.DataSource = @"(localdb)\MSSQLLocalDB";
        connStringBuilder.InitialCatalog = "WRESTLING.MDF";
        connStringBuilder.Encrypt = true;
        connStringBuilder.ConnectTimeout = 30;
        connStringBuilder.AsynchronousProcessing = true;
        connStringBuilder.MultipleActiveResultSets = true;
        connStringBuilder.IntegratedSecurity = true;

        string temp = @"Server=EC2AMAZ-FN5N011\MSSQLSERVER;Database=C:\APP_DATA\WRESTLING.MDF;Trusted_Connection=True;";
        string temp1 = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=C:\APP_DATA\WRESTLING.MDF;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
        string temp2 = @"Data Source = (local); AttachDbFilename = C:\APP_DATA\WRESTLING.MDF; Integrated Security = True; Connect Timeout = 30;";

        conn = new SqlConnection(temp2);
        comm = conn.CreateCommand();
}

Also, I am using an IIS Service to connect to the SQL database and that IIS is an Administrator too .

Update:

[enter image description here2

[enter image description here3

Upvotes: 1

Views: 341

Answers (1)

sepupic
sepupic

Reputation: 8697

Your code is the mix of connection strings to different servers. So it's not clear to which one you want to connect.

string temp and string temp2 attempt to connect to the default local instance of SQL Server. You should not attach nothing to it as there is already the database in question attached to this instance.

Your string temp1 attempts to connect to localdb, it's another server, not that one that we see on your screenshot, and here yes you can specify the file to attach.

Now it seems that you are trying to connect to the default instance under IIS APPPOOL\.NET v4.5 (your IIS is running under this account), this account is not the same with that you used to connect at the screenshot.

You should map this login to server (for now it's not mapped or at least not explicitely) and then map it to your database and make it db_owner:

create login [IIS APPPOOL\.NET v4.5] from windows;

use WRESTLING;
go

create user [IIS APPPOOL\.NET v4.5] from login [IIS APPPOOL\.NET v4.5];
exec sp_addrolemember 'db_owner', [IIS APPPOOL\.NET v4.5];

Upvotes: 1

Related Questions