October 5, 2006 at 11:41 am
Hi,
I'm having problems creating users. I can create a new database no problem, but I always seem to get problems with logins and users thereafter. For example, I just created a new database, then go and create a new user, mapping it to dbo and the newly created database. This bit is fine.
Then I get lost! Am I supposed to create a new User for that database? If I try to create a new user for that new database, I get another error like this:
Create failed for User 'SomeNewUser'. (Microsoft.SqlServer.Smo)
The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)
Can anyone tell me why I get this error message, as I don't remember ever giving this new user an account?
Many thanks
Gerard
October 5, 2006 at 12:01 pm
Are you working with one login or two? If you are working with same login in both steps, you have already given the login access to the database when you mapped it to DBO.
Then the next question I would have is does that account really need that level of rights?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 5, 2006 at 12:14 pm
Hi,
I'm not sure! My steps were this: I created a new database. Then I went to the main Security tab, Logins, and created a new login. This login I mapped to DBO and of course the new database. This is all I've done so far. Only that the user is having problems logging in.
I am not a SQL Server admin expert at all, I'm a developer, but unfortunately for me it's also my task to administer this thing every now and then!
I'd appreciate my next step really. Do I need to create a user for this database? Or a schema? The user needs to be able to create tables, and basically develop the database remotely.
Many thanks
October 5, 2006 at 2:29 pm
Are you sure that any account can access the SQL Server remotely?
What is the LOGIN name of the account that you mapped to DBO?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 5, 2006 at 2:52 pm
Yes, I can access the database remotely using the login I created from within Visual Studio or a local SQL Server Management Studio. The problem I am getting is trying to use a connection string in a web.config file in an asp.net website like this:
Server=.\SQLExpress;Persist Security Info=True; user id=someid;password=domepassword;initial catalog=3rdcovscouttroop
This is not working for some reason, and I keep getting a Could not connect to database error from within the website. So I wondered if I needed to do something else such as add a user or something.
October 5, 2006 at 2:56 pm
So, is the web server on the same box as the SQL Server instance? If not, you need to specify the Server name in the connection string.
Server=SERVERNAME\SQLExpress;Persist Security Info=True; user id=someid;password=domepassword;initial catalog=3rdcovscouttroop
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 5, 2006 at 3:09 pm
The web server is on the same box. In my view it should all work fine, because I have been using similar connections strings for other websites.
The only difference I can see is that this database does not have a seperate user, as with all my other databases. In all the other databases I created for other applications, I managed to somehow stumble on being able to create a user of the same name as the login, and that is what I attempted to do here, but couldn't. I just wanted some advice here on how to create a login, then a user and perhaps a schema if required. Then test the connection again from within the website connection string.
October 5, 2006 at 3:14 pm
OK, going back to a previous question, you said you created a user and mapped it to DBO. What Login is mapped to DBO?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 5, 2006 at 3:27 pm
I am not using the Express edition, but maybe the steps are similar:
1) come up with a brand new login and password
2) Expand the security section, right-click on Logins and choose new login
3) Fill in the new login name you just made up
4) Choose "SQL Server Authentication" and type in the new password you just made up
5) Click "User Mapping"
6) Check the database that want the account to have rights in
7) Choose the db_owner role and click "OK"
8) Modify your connection string with the new user name and password
The login should now have rights in the database.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 5, 2006 at 4:16 pm
Hi,
I'm afraid it's gone 11:00pm here in the UK and I'm off to bed!! Thanks so much for your help today. I reckon the problem is something to do with the website, as your instructions are what I've done really. I'll try again all day tomorrow! You are a star!!! Thanks 🙂 Gerard.
October 6, 2006 at 2:11 am
Another method:
Open the Security entry in Object Explorer (at the server level, not the database level).
Right-click on Logins and select New Login... Give it a name, select SQL Server authentication and give it a password.
For the moment, uncheck 'Enforce password policy'.
Set the default database to be your database
Press OK to save
Open the Databases entry in Object Explorer, open up your database under that, and open up Security under that again.
Right click on Users, select New User...
Click the ellipses (...) button next to the Login name radio button. Click Browse and find the user you just created. Tick the checkbox next to it and press OK and OK again
Make the user name the same as the login name
Give the user the appropriate Database role membership - db_datareader and db_datawriter would be common for a user, db_owner for a developer on a development system. Alternatively go to Securables and just give them access to the particular objects they'd need.
Press OK to save.
Not sure how close that is to what you've done, but it works fine every time for me if I'm creating a SQL login. Similar process for Windows authentication, just don't need to give a password, and you browse to find the login at the server level rather than typing it in (unless you've got a very good memory and are an accurate typist).
October 6, 2006 at 3:11 am
Hi Ian,
thank you very much indeed - just what the doctor ordered!
Have followed instruction and worked nicely.
Cheers
Gerard
October 6, 2006 at 8:43 am
Your connection string looks good. I have SQL Express on my box, but I don't use it. So I tried to set up a connection similar to yours. I created a new SQL Server login, then a new database owned by that login. In VS 2005, I tried to create a connection string for connecting, but it didn't work because Express is installed by default with Windows Authentication only turned on.
I had to go back to SQL Server Management Studio, right-click on the Server and choose Server Properties, Security, then select Server Authentication: SQL Server. Then restart the service (right-click on the Server and choose Restart).
If you don't have Mgmt Studio, I don't know how to change the authentication mode. I looked in Books Online, but if there is a way to do it in Transact-SQL, it's not easy to find.
October 6, 2006 at 8:52 am
Adding to my recent post, there is no Transact-SQL statement because it's a registry setting!
The Script button in Management Studio appears to produce the wrong registry setting, because that's not what's in there (at least not in my registry, but I have multiple instances installed). Go into Regedit and search for LoginMode. 1 is Windows Authentication, 2 is Mixed Mode Authentication. After you change it in the registry, restart the SQL Server Express service.
October 6, 2006 at 9:21 am
Grasshopper,
thanks for your post. I have SQL Server Management Studio and checked the SQL Server Express is indeed mixed mode security.
The whole issue started with me trying to install DotNetNuke on the website. I added what I thought was the correct connection strings to the web.config file, and then clicked on the default.aspx page in dotnetnuke install. This then starts an auto install of the required pages, files, etc. However, in this instance all I kept getting were connection problems. All this was on the web server.
On my own local box, everything works fine! So I'm at a loss as to why DNN doesn't connect. I did think it may be something I did by not setting up users correctly. The only thing it could be, is that I've got all the SQL Express database files in a single directory under the root C drive, and not in the individual website domain 'Data' folders. I don't know if this is the problem. However it shouldn't be, as the connection string tells all.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply