Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to add user to sql server 2005 express programatically Expand / Collapse
Author
Message
Posted Thursday, August 05, 2010 1:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 02, 2012 12:36 AM
Points: 54, Visits: 327
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
Post #964028
Posted Thursday, August 05, 2010 2:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 12:34 AM
Points: 188, Visits: 189
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
Post #964040
Posted Thursday, August 05, 2010 2:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 02, 2012 12:36 AM
Points: 54, Visits: 327
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?
Post #964058
Posted Thursday, August 05, 2010 3:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 12:34 AM
Points: 188, Visits: 189
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
Post #964101
Posted Thursday, August 05, 2010 4:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 02, 2012 12:36 AM
Points: 54, Visits: 327
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?
Post #964106
Posted Thursday, August 05, 2010 4:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 12:34 AM
Points: 188, Visits: 189
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
Post #964117
Posted Thursday, August 05, 2010 4:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 02, 2012 12:36 AM
Points: 54, Visits: 327
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.
Post #964128
Posted Thursday, August 05, 2010 4:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 12:34 AM
Points: 188, Visits: 189
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
Post #964131
Posted Thursday, August 05, 2010 5:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 02, 2012 12:36 AM
Points: 54, Visits: 327
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
Post #964145
Posted Thursday, August 05, 2010 5:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 12:34 AM
Points: 188, Visits: 189
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
Post #964154
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse