How to add user to sql server 2005 express programatically

Question

How to add user to sql server 2005 express programatically

niteshrajgopal

SSCommitted

Points: 1877
More actions
August 5, 2010 at 1:25 am

#238452

Hi Guys,
I have a class that checks if sql server express 2005 is installed on a pc and if not it installs it. In my class I specify stuff like sa password etc for the install.
When a user installed the software the database was created but he can't login as he gets a meesage saying:
unhandled exception has occurred in your application. Cannot open database "Games" requested by the login. The login failed.
How can I create a user for my database programmatically. Or even I run a sql script that creates the database. How can I do this in the script.
I'm just installing on a single pc, no networking required, so in my connectionstring I use .\SQLExpress as the server. please help

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Sudeepta SSCommitted Points: 1646 More actions · Answer 1

You actually need two credentials, one to connect to the SQL Server instance (Login) and one to connect to the database (User).

If you are using Windows Authentication to connect to the instance, then you can use the following command:

USE [master]

GO

CREATE LOGIN [test-pc\TestUser] FROM WINDOWS;

GO

USE [TestDB]

GO

CREATE USER [test-pc\TestUser] FOR LOGIN [test-pc\TestUser]

GO

-- The following command will grant db-owner rights for the above user

EXEC sp_addrolemember 'db_owner', [test-pc\TestUser] ;

GO

Hope, this may help.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

niteshrajgopal SSCommitted Points: 1877 More actions · Answer 2

thanks alot. That's exactly what I was looking for;-). How can I get the Windows User Name for example I want the user to be created like this:

SQL Server Name\User Name.

if I run that script you gave me will it solve my issue?

Sudeepta SSCommitted Points: 1646 More actions · Answer 3

You can not create a Windows Login account using the above script. You need to create the Windows Account using Computer Management (Start --> Run --> Compmgmt.msc).

Expand Users & Computers, Clilck on User, Right click and create User, say you created a user named 'User1'.

Then you can execute the above script, by replacing the login and user name as [test-pc\User1]

Hope, this may help.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

niteshrajgopal SSCommitted Points: 1877 More actions · Answer 4

say my PC Name is NITESH-PC. When I installed sql express it added NITESH-PC\NITESH to the logins.

I need to create a login for the user that is installing sql server. so if I was installing on a PC called TEST-PC with user TEST I would want to create a login in sql server as TEST-PC\TEST. is this possible?

Sudeepta SSCommitted Points: 1646 More actions · Answer 5

niteshrajgopal (8/5/2010)
say my PC Name is NITESH-PC. When I installed sql express it added NITESH-PC\NITESH to the logins.

It means you logged on to your PC with the user account NITESH. during the installation, you have added this account to the SQL Server Login.

so if I was installing on a PC called TEST-PC with user TEST I would want to create a login in sql server as TEST-PC\TEST. is this possible?

Yes, you can add the account with the above script.

However, if I remember correctly, the account using which you are installing the SQL Server 2005 Express edition, should be added as a login for the SQL instance.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

niteshrajgopal SSCommitted Points: 1877 More actions · Answer 6

Hi again,

if I remember correctly there is a check box that asks if you want to add the user to logins as they are not added by default.

Thing is i'm using a vb.net class to do the install so I have to set the login in my script.

Sudeepta SSCommitted Points: 1646 More actions · Answer 7

If the Windows account is already exist on your system, then you can use the above script to add it to SQL Server, and assign the required permissions.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

niteshrajgopal SSCommitted Points: 1877 More actions · Answer 8

sorry but i'm a bit lost. Is there anyway to get the account info of the user logged into windows in sql server?

Maybe I should tackle this differently. What do you think is an easire way to get around my problem

Sudeepta SSCommitted Points: 1646 More actions · Answer 9

Is there anyway to get the account info of the user logged into windows in sql server?

You can not logged in to SQL Server, until you have a vaild login for the SQL Server instance. An user logged in to the Windows machine(using his Windows Login account), needs to have his windows login added to the SQL server before he can access the SQL instance. Is that what you are asking.

Sorry, if I didn't understand your requirement.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

niteshrajgopal SSCommitted Points: 1877 More actions · Answer 10

Hi again,

I've added the followinbg to my script

USE [master]

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'Reality')

BEGIN

-- Create user for SQL Authentication

CREATE LOGIN Reality WITH PASSWORD = 'Games12er' ,DEFAULT_DATABASE = [Games]

-- Add User to first database

USE Games;

CREATE USER Reality FOR LOGIN Reality;

EXEC sp_addrolemember 'db_datareader', 'Reality'

EXEC sp_addrolemember 'db_datawriter', 'Reality'

END

I figured it is easier to use sql authentication. Please advise me if this is correct. it seems to have worked on my pc. still have to test on a users pc

Sudeepta SSCommitted Points: 1646 More actions · Answer 11

It is correct.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

niteshrajgopal SSCommitted Points: 1877 More actions · Answer 12

thanks for all your help. I've learnt alot from you;-)

phil 78969 Valued Member Points: 61 More actions · Answer 13

phil 78969

Valued Member

Points: 61

August 18, 2010 at 6:47 am

#1208439

(edit)sorry posted reply to wrong thread.

developer 34412 Newbie Points: 4 More actions · Answer 14

Hey thanks for that, have been trawling for ages to find this code. Very useful. 🙂