SQL Server Script to create a new user

Solution 1:

Based on your question, I think that you may be a bit confused about the difference between a User and a Login. A Login is an account on the SQL Server as a whole - someone who is able to log in to the server and who has a password. A User is a Login with access to a specific database.

Creating a Login is easy and must (obviously) be done before creating a User account for the login in a specific database:

CREATE LOGIN NewAdminName WITH PASSWORD = 'ABCD'
GO

Here is how you create a User with db_owner privileges using the Login you just declared:

Use YourDatabase;
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NewAdminName')
BEGIN
    CREATE USER [NewAdminName] FOR LOGIN [NewAdminName]
    EXEC sp_addrolemember N'db_owner', N'NewAdminName'
END;
GO

Now, Logins are a bit more fluid than I make it seem above. For example, a Login account is automatically created (in most SQL Server installations) for the Windows Administrator account when the database is installed. In most situations, I just use that when I am administering a database (it has all privileges).

However, if you are going to be accessing the SQL Server from an application, then you will want to set the server up for "Mixed Mode" (both Windows and SQL logins) and create a Login as shown above. You'll then "GRANT" priviliges to that SQL Login based on what is needed for your app. See here for more information.

UPDATE: Aaron points out the use of the sp_addsrvrolemember to assign a prepared role to your login account. This is a good idea - faster and easier than manually granting privileges. If you google it you'll see plenty of links. However, you must still understand the distinction between a login and a user.

Solution 2:

Full admin rights for the whole server, or a specific database? I think the others answered for a database, but for the server:

USE [master];
GO
CREATE LOGIN MyNewAdminUser 
    WITH PASSWORD    = N'abcd',
    CHECK_POLICY     = OFF,
    CHECK_EXPIRATION = OFF;
GO
EXEC sp_addsrvrolemember 
    @loginame = N'MyNewAdminUser', 
    @rolename = N'sysadmin';

You may need to leave off the CHECK_ parameters depending on what version of SQL Server Express you are using (it is almost always useful to include this information in your question).