How to grant user sys.admin role in SQL via Entity Framework?

Question

Just to clear the wind I am aware off that granting users sysadmin in T-SQL in not a good thing but per customer requirements it needs to be done.

I have this :

public void AddUserToServer(List<string> users, List<string> servers)
{
        foreach (var server in servers)
        {
            setConnection(server);

            foreach (var user in users)
            {
                string sql =
                    "USE [master]" +
                    " CREATE LOGIN" + " [TP1\\" + user + "] FROM WINDOWS WITH DEFAULT_DATABASE=[master], " +
                    "DEFAULT_LANGUAGE=[us_english] " +
                    "EXEC sys.sp_addsrvrolemember @loginame = N'TP1\\" + user + "', @rolename = N'sysadmin'";

                _dbContext.Database.ExecuteSqlCommand(sql);
            }
        }
}

I got the above script by creating the user manually in ssms and then generated the script. However when running it I am getting this current error:

'The procedure 'sys.sp_addsrvrolemember' cannot be executed within a transaction.

So I changed it to :

            string sql =
                "USE [master]" +
                " CREATE LOGIN" + " [TP1\\" + user + "] FROM WINDOWS WITH DEFAULT_DATABASE=[master], " +
                "DEFAULT_LANGUAGE=[us_english] " +
                "GRANT ADMINISTER BULK OPERATIONS TO[TP1\\" + user + "]";

This creates the user BUT I do not get the sysadmin role.

How do I solve this?

score 1 · Accepted Answer · answered Oct 22 '18 at 21:04

So typical that I spent some time on this and finally after positing I find the answer.

I believe that this is a question more people will ask so I can provide what I found:

    string sql =
    "USE [master]" +
    " CREATE LOGIN" + " [TP1\\" + user + "] FROM WINDOWS WITH DEFAULT_DATABASE=[master], " +
    "DEFAULT_LANGUAGE=[us_english] " +
     "EXEC sys.sp_addsrvrolemember @loginame = N'TP1\\" + user + "', @rolename = N'sysadmin'";


    _dbContext.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, sql);

NOTE:

TransactionalBehavior.DoNotEnsureTransaction

Starting with EF6 Database.ExecuteSqlCommand() by default will wrap the command in a transaction if one was not already present. There are overloads of this method that allow you to override this behavior if you wish. Also in EF6 execution of stored procedures included in the model through APIs such as ObjectContext.ExecuteFunction() does the same (except that the default behavior cannot at the moment be overridden).

Found the answer here

How to grant user sys.admin role in SQL via Entity Framework?

1 Answers1