Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
1,302 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 2000 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 11

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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