Solved

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

Posted on 2009-03-31
17
1,246 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

17 Experts available now in Live!

Get 1:1 Help Now