Solved

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

Posted on 2009-03-31
17
1,267 Views
Last Modified: 2012-05-06
In my C# program, I need to create an SQL Server Login. That is, I need to do exactly what you'd do using SQL Server Management Studio. How can I do that?
0
Comment
Question by:sachintha81
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
17 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 24026421
0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24026450
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.
0
 
LVL 3

Author Comment

by:sachintha81
ID: 24026567
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?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 3

Author Comment

by:sachintha81
ID: 24026623
QPR, I am trying what's in the link you have provided. Thanks a lot!
Will let you know if it works or not...
0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24026638
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.
0
 
LVL 3

Author Comment

by:sachintha81
ID: 24036159
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?
0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24040324
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 

Open in new window

0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24040344
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

Open in new window

0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24040351
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

Open in new window

0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24040369
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
0
 
LVL 3

Author Comment

by:sachintha81
ID: 24045772
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?
0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24047624
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
0
 
LVL 3

Author Comment

by:sachintha81
ID: 24056971
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

Open in new window

0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24076209
Hi,
"Alter" is for when you want to modify/update the SP. Use "Create" instead when it doesn't exist.
0
 
LVL 3

Author Comment

by:sachintha81
ID: 24084852
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.
0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24124926
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
0
 
LVL 3

Accepted Solution

by:
sachintha81 earned 0 total points
ID: 24342557
Never mind, I figured it out.
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

729 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