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
922 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
  • 2
4 Comments
 
LVL 14

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now