How to Create a User in SQL Server Express Database I Added to My Project

How can I create a user in SQL Server Express database I added to my project?

You would need to create a SQL Authenticated login first with CREATE LOGIN then add a user associated with that login to your database by using CREATE USER.

USE [master]
GO
CREATE LOGIN [JohnEgbert] WITH PASSWORD=N'YourPassword', 
                 DEFAULT_DATABASE=[YourDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [YourDB]
GO
CREATE USER [JohnEgbert] FOR LOGIN [JohnEgbert] WITH DEFAULT_SCHEMA=[dbo]
GO

How to attach sql server express database to project in entity framework

Execute this command in your SQL Server Management Studio to locate the physical database and it's log file location.

SELECT * FROM sys.database_files

Else

It you are running a 32-bit SSMS then you can find all database in following path.

C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA

Note: Before you copy the database you need to disconnect all connection in SMSS referring this database.

How do I point to my full SQL Server Express database in ASP.NET Core project?

If the Core Application and SQL Express database are on the same server, you'll want to change the Connection String to: .\SQLExpress. This will automatically target the localhost.

Which will indicate the Express SQL Instance.

How do I create a new user in SQL Server 2012 that I can use in a connection string?

Before you add the user to the db, add the user to the server -- under the server, right click the security folder, and try it that way. Then you can grant permissions to the appropriate databases to that user

No SQL Server Express logins have permission to create db

I assume SQL Authentication is disabled and builtin\users is not sysadmin (ie. you locked yourself out).

Stop the Express service, start the server in admin mode (sqlservr -m -s SQLEXPRESS from command line), connect to the admin instance and add builtin\administrators back as sysadmin. Then stop the administrative instance (Ctrl-C) and start back the the normal instance.

Since Vista/Win7/Win2k8 remove the administrator token from your context under normal UAC, it makes sense to also add as sysadmin yourself explicitly (domain\user) so you don't have to use RunAsAdministrator to connect.

See How to add a Windows user to the sysadmin fixed server role in SQL Server 2005 as a failure recovery mechanism.

How to add Active Directory user group as login in SQL Server

In SQL Server Management Studio, go to Object Explorer > (your server) > Security > Logins and right-click New Login:

Then in the dialog box that pops up, pick the types of objects you want to see (Groups is disabled by default - check it!) and pick the location where you want to look for your objects (e.g. use Entire Directory) and then find your AD group.

You now have a regular SQL Server Login - just like when you create one for a single AD user. Give that new login the permissions on the databases it needs, and off you go!

Any member of that AD group can now login to SQL Server and use your database.

How to create new database in SQL Server Express 2008 and allow for connecting?

1. don't try to map dbo to your login. dbo is a special user.

2. To connect to SQL Server using SQL authentication, you need to come in via a login, not a user (let's leave contained databases in Denali out of the discussion for now). So you should never be trying to specify a database user (like dbo) in your connection string. I highly recommend creating a login and then creating a user at the database level with the same name that matches the login - this is far less confusing than trying to map login_foo to user_bar. I also recommend staying away from special words for users or logins (e.g. dbo).

3. I recommend learning the DDL and stored procedures necessary for setting this stuff up, and stop pointing and clicking through the UI. While the UI may seem quicker for some tasks, it's terribly difficult to reproduce what you've done exactly, and it takes a lot of teeth-pulling to figure out what options you've set on the tabs in the dialogs that we can't see. If you use a script, you can post a script and we can see it all without asking further questions, and you can also save that script and refer to it (which is going to be at least as good as your memory, but almost certainly better).

Here is how I would add a server login, put them in the newdb database as a user, add them to the db_owner (not dbo) role, and set their default database to newdb. Open a new query window in Management Studio and connect to master, then run the following:

USE [master];
GO
CREATE LOGIN Ropstah_test WITH PASSWORD = 'secure password';
GO
USE newdb;
GO
CREATE USER Ropstah_test FROM LOGIN Ropstah_test;
GO
EXEC sp_addrolemember 'db_owner', 'Ropstah_test';
GO
USE [master];
GO
ALTER LOGIN Ropstah_test WITH DEFAULT_DATABASE = newdb;
GO

Now your connection string from .NET should look like:

Data Source=.\SQLEXPRESS;Initial Catalog=newdb;User ID=Ropstah_test;Password=secure password;

If that doesn't work, I'd validate that your ASP.NET app is running on the same machine as the SQL Express instance. The web server is also on your machine, right? Short of that, this should work, and if it's not working, there's some other variable involved that is not obvious. You should not have to make the user the actual database owner to connect, but if the above still doesn't solve the problem, see if the below script changes the behavior or at least the error message that's recorded in the log:

USE newdb;
GO
DROP USER Ropstah_test;
GO
USE [master];
GO
ALTER AUTHORIZATION ON DATABASE::newdb TO Ropstah_test;

This drops the user from the database and then re-adds them as the official owner (this is not the same thing as being in the db_owner role, which is essentially just shorthand to grant a template of permissions). This should not be necessary but it might help isolate why you can't get this usually simple process to work.

How to incorporate the SQL Server Express installer in my own setup?

Check out this one: Include another MSI file in my setup project

How to deploy application with sql server database on clients

Option 1 - Setup Project

Using Visual Studio you can create a setup project and install prerequisites that you need during installation.

The installation process is very simple and the end user can install application and prerequisites after clicking next buttons.

Here are the steps for Creating a Setup Project:

1- Create a c# Windows Forms Application

1. Create a C# Windows Forms Project
2. Add New Item and Add SQL Server Database to your application
3. Add a table to your application and fill some data in it
4. Show the data in your main form.

2- Create a Setup Project

1. Add new project → setup and deployment → setup project
2. Right Click on Setup project and Add project Output and select primary output from your main project
3. Right Click on Setup project and Add project Output and select content files from your main project
4. Right CLick on setup project and Click Properties and click Prerequisites and select SQL Server Express
5. Select .Net Framework
6. Select Windows Installer
7. Select radio button Download prerequisites from the same location as my application.
8. Right Click on Users Desktop at left pane and add new Shortcut and select application folder, primary output from SampleApplication, and click ok and the rename the short cut to what you need.
9. Rebuild solution.
10. Rebuild Setup Project
11. Go to Output directory of setup project and run setup.exe

It's that easy.

For more information take a look at following docs articles:

1. How to: Create or Add a Setup Project
2. How to: Install Prerequisites in Windows Installer Deployment
3. Walkthrough: Using a Custom Action to Create a Database at Installation

Option 2 - ClickOnce

Using Visual Studio another option is using ClickOnce publishing.

To do so, in properties of your project, in publish tab, click prerequisites button, you can select SQL Express in prerequisites. This way, you only need to set your database files to copy in output directory, and use AttachDbFileName in connection string: Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Database.mdf; Initial Catalog=Master".


For more information take a look at the following docs article:

• How to: Publish a ClickOnce Application using the Publish Wizard

---

Related Topics