Solved

I am trying to add a login to a sql server express database but am getting an error

Posted on 2014-07-21
4
1,076 Views
Last Modified: 2014-07-22
I don't know a lot about SQL server Express but I need to create a login account so that I can connect with something other than windows authentication. I'm trying the following (password blanked out for security) but I'm getting errors. Does anyone know what I'm doing wrong? Nothing is connected to the database other than SQL Server Management Studio.

USE [master]
GO
CREATE LOGIN [LEadmin] WITH PASSWORD=N'******' MUST_CHANGE, DEFAULT_DATABASE=[LE_tool_sql2SQL], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'LEadmin', @rolename = N'dbcreator'
GO
EXEC master..sp_addsrvrolemember @loginame = N'LEadmin', @rolename = N'securityadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'LEadmin', @rolename = N'sysadmin'
GO
USE [LE_tool_sql2SQL]
GO
CREATE USER [LEadmin] FOR LOGIN [LEadmin]
GO

Getting the following errors

Msg 945, Level 14, State 2, Line 1
Database 'LE_tool_sql2SQL' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 68
'LEadmin' is not a valid login or you do not have permission.
Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 68
'LEadmin' is not a valid login or you do not have permission.
Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 68
'LEadmin' is not a valid login or you do not have permission.
Msg 945, Level 14, State 2, Line 1
Database 'LE_tool_sql2SQL' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 15007, Level 16, State 1, Line 1
'LEadmin' is not a valid login or you do not have permission.
0
Comment
Question by:boukaka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 500 total points
ID: 40210786
Hi,

There may be two reasons for this
1. That you don't have Sysadmin rights to the database on which you are trying to create login if user is not SA

Other there might be wrong status stored in the data base which you can clear with the following script ..

See the DB status, it should return 1

use master
select databaseproperty('LE_tool_sql2SQL','isShutdown')

Open in new window


2. Change the database to offline to clear the db status

use master
alter database  LE_tool_sql2SQL set offline

Open in new window


3. Now change the database to online, at this step log file and data files will be verified by sql server

use master
alter database LE_tool_sql2SQL set online

Open in new window

0
 
LVL 10

Expert Comment

by:Ray
ID: 40211583
Not a solution, but a tip... Since you say you have management studio, just use the GUI to add the ID.  Do it at the server level under the security folder.  If you want the login to have privileges only in a specific DB, just mark those in the User Mapping section of the create user window.
0
 

Author Comment

by:boukaka
ID: 40212793
I checked the status and it returned 0, not sure what to do about that? You said it should be 1
0
 

Author Closing Comment

by:boukaka
ID: 40212814
AH! That worked! I ran the offline script, then the online script then retried my create login script and it worked. Thank you so much!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

735 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