How to execute a Transact-SQL command in C#?

I've been told that executing the following T-SQL script in C# will create a new login (Windows based) to SQL Server.

So how do I get taht to work?
I have the following questions.

1. Where do I put that code?
2. Is "DomainName" the same as my machine name?
3. Is  "loginName" the new login name that I want to create?

Thanks in advance.
CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;
GO

Open in new window

LVL 3
sachintha81Asked:
Who is Participating?
 
HarryNSCommented:
Use the following code...
private void button1_Click(object sender, EventArgs e)
        {
            if (CreateLoginFromWindows("<USER>", "<DOMAIN>", "Master"))
            {
                MessageBox.Show("Login Created for the User <USER>");
            }
            else
            {
                MessageBox.Show("Failed to create Login for the User <USER>");
            }
        }
        private int CreateLoginFromWindows(string strUser, string strDomain, string strDatabase)
        {
            try
            {
                string strConn = "Data Source=<DB>;Initial Catalog=master;Integrated Security=True";
 
                string strCreateUser = @"use " + strDatabase + " CREATE LOGIN [" + strDomain + "\\" + strUser + "] FROM WINDOWS;";
 
                using (SqlConnection sqlConn = new SqlConnection(strConn))
                {
                    sqlConn.Open();
                    SqlCommand sqlCmd = new SqlCommand(strCreateUser, sqlConn);
                    sqlCmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch 
            {
                return false;
            }
        }

Open in new window

0
 
HarryNSCommented:
To get the current user and domain you can use this,
string strUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

I Have modified the previous code to this and pasted below. Check this.
private void button1_Click(object sender, EventArgs e)
        {
            string strUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
            if (CreateLoginFromWindows(strUser, "Master"))
            {
                MessageBox.Show("Login Created for the User " + strUser);
            }
            else
            {
                MessageBox.Show("Failed to create Login for the User <USER>");
            }
        }
        private bool CreateLoginFromWindows(string strDomUser, string strDatabase)
        {
            try
            {
                string strConn = "Data Source=<DB Server>;Initial Catalog=master;Integrated Security=True";
 
                string strCreateUser = @"use " + strDatabase + " CREATE LOGIN [" + strDomUser + "] FROM WINDOWS;";
 
                using (SqlConnection sqlConn = new SqlConnection(strConn))
                {
                    sqlConn.Open();
                    SqlCommand sqlCmd = new SqlCommand(strCreateUser, sqlConn);
                    sqlCmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch 
            {
                return false;
            }
        }

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
sachintha81Author Commented:
Harry, I ran your code but it gives an exception at the line sqlCmd.ExecuteNonQuery().

It says "The server principal 'XXX' already exists. Changed database context to MyDB"
The Inner Exception seems to be null.

Here, XXX is my current user (the value in strDomUser) and MyDB is the DB I'm associated with (value of strDatabase).

As for the connection string I put Data Source as the current SQL Server account I'm logged in (which goes as "MachineName\InstanceName" ) and Initial Catalog as MyDB.

Did I make any mistake with any of those parameters? Why am I getting this exception.

Help greately appreciated.
0
 
RiteshShahCommented:
are you trying to create LOGIN which is already exist? have you tried my code?
0
 
sachintha81Author Commented:
Yes Ritesh but that too gives me the same exception.

Actually I must say I'm confused here. I'm not sure about those parameters, whether I put them correctly or not.
What I want done is just to create a new login (the name should be of my choice) to the SQL Server. So should I not put my parameters as stated above? Should they be different?
0
 
RiteshShahCommented:
if you have observed my code, I am not only making LOGIN but USER as well for perticular database. I think you are trying to make login which is already exist and that is why exception occur. Can you try creating LOGIN which is not exist?
0
 
sachintha81Author Commented:
Thanks Ritesh, this time I got it to work!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.