?
Solved

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

Posted on 2009-04-28
8
Medium Priority
?
327 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:sachintha81
  • 3
  • 3
  • 2
8 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 1500 total points
ID: 24248822
0
 
LVL 6

Expert Comment

by:HarryNS
ID: 24249045
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
 
LVL 6

Expert Comment

by:HarryNS
ID: 24249231
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Author Comment

by:sachintha81
ID: 24267408
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24267426
are you trying to create LOGIN which is already exist? have you tried my code?
0
 
LVL 3

Author Comment

by:sachintha81
ID: 24267444
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24267455
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
 
LVL 3

Author Comment

by:sachintha81
ID: 24267586
Thanks Ritesh, this time I got it to work!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Integration Management Part 2
Loops Section Overview

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question