How to create an SQL Server Login from within C#?

You should use script to do so, and use ADO.NET to run that script.

The T-SQL script that creates such a login is:
1. Creating a login from a Windows domain account
CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;
2. Creating a login with a password
CREATE LOGIN <loginName> WITH PASSWORD = '<enterStrongPasswordHere>';

of couse, you should connect to the database with another user credentials, then create your new login, and then close the open connection and open a new one, with the new user credentials.

I do not know why you need to create a login from within your application. This is not a best practice, unless you are coding some sort of a back office for databases. If you need an advice about that, let me know.

ASKER

amazingwolf1, yes I am creating sort of a thing you mention.

I have a C# program which creates a setup program integrating a lot of other C# codes. But at the moment, after I run there setup program and install it, I have to manually perform some settings using SQL Server Management Studio everytime I install that program. So, now I have to integrate that process also to this setup so I dont have to do it seperately and manurally everytime.

Btw I am not really familiar with T-SQL scripts, who can you please help with that?

ASKER

QPR, I am trying what's in the link you have provided. Thanks a lot!
Will let you know if it works or not...

Hi,
I think I can help you with the scripts, let me know exactly what we're talking about :-)

About the setup project, I don't know if you are familiar with Installer classes that can be used to perform such operations during installation:
http://live.mscommunity.net/blogs/notequ/archive/2008/06/05/installer-class.aspx

Let me know if you need any further assistance.

ASKER

amazingwolf1, this is what I need to be done.

As I said above, this program creates a setup file, and it is completed before by someone. Now I need to add the following functionality.

In the program, it creates an SQL Server Database. Say, MYDB.
After it is created ( I need to check whether it is correctly created as well) I need to create an SQL Server Login with following properties, from within my C# program.

Info:
Login Authentication : Windows
Login Name : If it is Windows 2003 / Vista - WIN2003_VISTA
If it is Windows 2000 / XP - WIN2000_XP

Database : MYDB

User Mapping : Database is MYDB. Roles : db_owner, public

Schema : MYDB

Current Status : Authorized, Login = valid

Can you do that?

Here is a script to create a db - you should work on it a little to make it generic it you need to

USE [MASTER]
GO
CREATE PROCEDURE PSP_CREATE_DATABASE
AS
-- TO MAKE THIS SP GENERIC, SEND IT A PARAMETER OF @DB_NAME. THEN, YOU SHOULD CREATE THE SAME
-- SCRITP YOU SEE HERE A DYNAMIC SCRIPT (E.G. CONCAT INTO A STRING) TO REPLACE THE DB NAME
-- WITH YOUR PARAMETER. THEN, YOU SHOULD EXECUTE YOUR STRING USING THE EXEC COMMAND
 
/****** Object:  Database [MyDB]    Script Date: 04/01/2009 14:51:28 ******/
CREATE DATABASE [MyDB] ON  PRIMARY 
( NAME = N'MyDB_Data', FILENAME = N'C:\DB\MyDB_Data.MDF' , SIZE = 2000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 80KB )
 LOG ON 
( NAME = N'MyDB_Log', FILENAME = N'C:\DB\MyDB_Log.LDF' , SIZE = 1000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'idex', @new_cmptlevel=80
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MyDB].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [MyDB] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [MyDB] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [MyDB] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [MyDB] SET ARITHABORT OFF 
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [MyDB] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [MyDB] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [MyDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [MyDB] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [MyDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [MyDB] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [MyDB] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [MyDB] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [MyDB] SET  DISABLE_BROKER 
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [MyDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [MyDB] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [MyDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [MyDB] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [MyDB] SET  READ_WRITE 
GO
ALTER DATABASE [MyDB] SET RECOVERY FULL 
GO
ALTER DATABASE [MyDB] SET  MULTI_USER 
GO
ALTER DATABASE [MyDB] SET PAGE_VERIFY TORN_PAGE_DETECTION  
GO
ALTER DATABASE [MyDB] SET DB_CHAINING OFF

Now here are scripts to create your user - first create the login

use [master]
go
alter PROCEDURE [dbo].[Windows_New_Login]
 
	@Loginname nvarchar(255),
	@DeafaultDatabase nvarchar(255)
AS
      SET NOCOUNT ON
      SET XACT_ABORT ON
declare @exec  varchar(500)
 
set @exec='CREATE LOGIN [' + @Loginname + '] FROM WINDOWS WITH DEFAULT_DATABASE = ' + @DeafaultDatabase 
EXEC(@exec)
GO

Now create this user on your db:

use [mydb]
go
--call the sp to create new user:
--[User_Insert] 'NewUser','NewPass123','MyDB'
--check the newly created user:
--sp_helpuser 'NewUser'
alter PROCEDURE [dbo].[User_Insert]
 
	@Loginname nvarchar(255),
	@Password nvarchar(255),
	@DeafaultDatabase nvarchar(255)
AS
      SET NOCOUNT ON
      SET XACT_ABORT ON
declare @exec  varchar(500)
--You must also add a user account for the logon in the database, as shown in the following code sample.
set @exec='CREATE USER ''' + @Loginname  + ''' FOR LOGIN [' + @Loginname + '] '
 
exec sp_grantdbaccess @Loginname -- 'gives access to the current DB
exec sp_addrolemember 'db_owner',@Loginname --'give role 'db_owner' to joe1 in the current DB

sachintha81,
I posted some scripts for you as stored procedures. You will have to change whatever needs changing there, I managed to test most of it.

HTH

ASKER

Amazingwolf, I'm new to this script thing so please can you give me some assistance in that?
I mean how to run a script?

Use microsoft sql server management studio to test the SPs I sent you.
To call an SP, use the format:

SP_Name param1, param2, param3

To use the SPs from your code:
http://www.codeproject.com/KB/cs/simplecodeasp.aspx

Read more about creating a database and the logins:
http://www.aspmessageboard.com/showthread.php?t=162831
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/sqlserver2005logins/2474/

HTH

ASKER

Amazingwolf, there is another favor needed, if you could please

I have some problem understading the SP you've given me to create a login. It says ALTER procedure, but to alter you already have to have that one doesnt it? Also, whats the name of the procedure?

Thanks!

use [master]
go
alter PROCEDURE [dbo].[Windows_New_Login]
 
        @Loginname nvarchar(255),
        @DeafaultDatabase nvarchar(255)
AS
      SET NOCOUNT ON
      SET XACT_ABORT ON
declare @exec  varchar(500)
 
set @exec='CREATE LOGIN [' + @Loginname + '] FROM WINDOWS WITH DEFAULT_DATABASE = ' + @DeafaultDatabase 
EXEC(@exec)
GO

Hi,
"Alter" is for when you want to modify/update the SP. Use "Create" instead when it doesn't exist.

ASKER

Experts, forgive me my incompetence, but I still have trouble with this.
I can't figure out what is wrong with this, so is there any other way you can do this without a stored procedure?
A code example would be greatly appreciated.

Hi sachintha81,
I recommend you would dive into the basics of stored procedures - it's really not that hard, and it is efficient and good for maintanance issues.

I sent you a few links to help you get started already. If you have specific questions then post them here, otherwise I suggest you read more about this topic.

HTH

ASKER CERTIFIED SOLUTION