Solved

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

Posted on 2009-03-31
17
1,249 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb latch contention 12 50
Not showing page correctly 3 31
scanning dentists xray (the small ones) 3 45
Sql Query Datatype 2 19
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now