How to change settings of SQL Logins using Visual C# code?

[sachintha81]

First I created a Login to SQL Server using a T-SQL command from within C#.
This is the query I used.

CREATE LOGIN NT AUTHORITY\NETWORK SERVICE FROM WINDOWS WITH DEFAULT_DATABASE="MyDB";

Here, NT AUTHORITY\NETWORK SERVICE is the name of the Login I created and MyDB is the DataBase I associate it with.

After creating it I need to change some settings of the created login. Following is the manual procedure I usually do.

1. Open SQL Server Management Studio
2. In the "Object Explorer", expand "Security" -> "Logins"
3. Right Click the newly created login "NT AUTHORITY\NETWORK SERVICE" -> Select "Properties"
4. In the "Select a page" section select "User Mapping"
5. In the right hand side, there is a section called "Users mapped to this login". There, under the "Map" section check the CheckBox relavent to MyDB, then under "Default Schema" click the button that appears.
6. In the appearing "Select Schema" windown, click "Browse", select MyDB from the new window that opens and click OK. Then press OK in the "Select Schema" window and return to the previous window. (Bear in mind that at the time of the creation of MyDB, I also create Schemas, so that I am able to select that from "Select Schema" window. In your PC, you may not be able to do so if you don't create a schema when you create a DB.)
7. There, in the part at the bottom which says "Database role membership for : MyDB", check "db_owner". ("public" is already checked, so leave it as it is)
8. Then click OK in the Login Properties and apply the above settings.

So, what I need to be done is do the exact same procedure using MS Visual C# codes instead of using the Wizard. In other words I need to automate the above said procedure. Can someone please show me the way.

Help is greatly appreciated.

[sknake]

The raw SQL for doing this task is:

USE [master]
GO
ALTER LOGIN [myUser] WITH DEFAULT_DATABASE=[myDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, NO CREDENTIAL
GO
USE [myDB]
GO
CREATE USER [myUser] FOR LOGIN [myUser]
GO
USE [myDB]
GO
ALTER USER [myUser] WITH DEFAULT_SCHEMA=[mySchema]
GO
USE [myDB]
GO
EXEC sp_addrolemember N'db_owner', N'myUser'
GO

To call the query from code use:

private static void ExecuteSQL(string query)
    {
      ExecuteSQL(query, "Leather");
    }
    private static void ExecuteSQL(string query, string db)
    {
      string connStr = string.Format(@"Data Source=apex2006sql;Initial Catalog={0};Integrated Security=True;", db);
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.ExecuteNonQuery();
        }
        conn.Close();
      }
    }
    private void simpleButton3_Click(object sender, EventArgs e)
    {
      ExecuteSQL(@"ALTER LOGIN [myUser] WITH DEFAULT_DATABASE=[myDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, NO CREDENTIAL", "master");
      ExecuteSQL(@"CREATE USER [myUser] FOR LOGIN [myUser]");
      ExecuteSQL(@"ALTER USER [myUser] WITH DEFAULT_SCHEMA=[mySchema]");
      ExecuteSQL(@"EXEC sp_addrolemember N'db_owner', N'myUser'");
    }

In the future you can figure out the SQL used within SQL Server Management Studio. At the top of all windows you will see a drop down button labeled "script". Click it and select the menu item "Script action to clipboard". This will copy the raw SQL I pasted here to your clipboard.

[sachintha81]

Thank you so much sknake, it just worked like a charm!

[sknake]

I'm glad you have it working, and good luck in the future!

Please mark this thread if I have answered your question.

[sachintha81]

Oh sorry I didn't know I was supposed to do that!
Just did it!
:)

[sachintha81]

Mate, can I get another thing cleared?

In the raw SQL above, a login/user is created without checking whether it exists or not. But I need that too (checking if it exists) done when creating a login/user.

I was able to sort it for a user as follows:

USE [BackStreet]
GO
IF NOT EXISTS (select loginname from master.dbo.syslogins where name = N'NT AUTHORITY\NETWORK SERVICE' and dbname = 'BackStreet')
CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS WITH DEFAULT_DATABASE=[BackStreet]
GO

But when I try the same for a user, it fails.
So how do I check whether a user exists or not before creating the users, using

IF NOT EXISTS

?

[sachintha81]

More info:

The DataBase I use here is a DB called "BackStreet"

So I'm not sure if

select loginname from master.dbo.syslogins where name =

is correct. I mean, are all the logins that are created associated with the "master" database?

So again, I want to know how to check if the login and the user exists before creating them.

Help please!