Added User but cannot Connect on 2005? 2

[litton1]

Hi all, first I will apologies if this a simplistic question…. Here goes. I need to learn SQL Server 2005 for work so I have installed and created a DB without much trouble, so far so good. I created a new Login user on this local machine with the following commands

CREATE LOGIN myUser with PASSWORD = 'password'

use Forum
CREATE USER myUser

go
grant connect to myUser
go

Alter login myUser with CHECK_EXPIRATION = off
go

alter LOGIN myUser with CHECK_POLICY = OFF
go

When I try to switch users from within management studio I am unable to connect. Does anyone know why this is? I am not doing something. Thx in advance T

Age is a consequence of experience

 

[ptheriault]

I believe your syntax should look like this.

use Forum
CREATE USER myUser FOR LOGIN myUser WITH DEFAULT_SCHEMA = dbo

you might want to give the user read\write access.

EXEC sp_addrolemember 'db_datareader', 'myUser '
GO
EXEC sp_addrolemember 'db_datawriter', 'myUser '
GO

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[litton1]

ok thx i will give that a go, do i need to drop the user first before running that?

Age is a consequence of experience

 

[ptheriault]

drop the user from the database. You don't have to delete the login from the server.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[litton1]

ok thx, I will try that when i get home.

Age is a consequence of experience

 

[litton1]

What is the syntax to drop the user? I tried

Use forum
go
drop user myUser

But I get a message telling me:

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

Age is a consequence of experience

 

[ptheriault]

That's the correct syntax but you have to drop the schema first.

DROP SCHEMA myUser

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[litton1]

hmmm...
ok lol now when i go:

use forum
go
drop schema myUser

i get:
Msg 15151, Level 16, State 1, Line 1
Cannot drop the schema 'myUser', because it does not exist or you do not have permission.

but i own the DB i think?

Age is a consequence of experience

 

[ptheriault]

Open SQL Server Management Studio,
Navigate to the forum database.
click on the security folder and then the schema folder.
On the Summary page you will see all the schemas in the database. Does the user MyUser own any of them?
If so double click on that schema and change the owner.
The schema owner will be the same as the schema name.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[litton1]

Ok all is going well! I have done as you said and now the user has been Totally dropped
Tomorrow i will make a new user and let you know if all goes well. Thx T

Age is a consequence of experience

 

[litton1]

Ok I ran the commands as was given at the top but when I try to login I still get "The user is not associated with a trusted connection.
Any suggestions?

Age is a consequence of experience

 

[ptheriault]

log in to SSMS with you admin account. From a query window run the following commands.

-- Set the execution context to myUser. 
EXECUTE AS LOGIN = 'myUser';
--Verify the execution context is now myUser.
SELECT SUSER_NAME(), USER_NAME();
--exec a query the my

I've seen that error before but only when the user account added was a domain account and the server was not a member of the domain.




- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[litton1]

That’s interesting… the result is that myUser is a Guest. Does this mean he is not trusted?

Age is a consequence of experience

 

[ptheriault]

add your user to the data reader role and try again.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

You created a SQL Account, but you are getting trust errors. Trust errors come into play with domain accounts. It sounds like you have created a SQL account with the same name as the domain account and something is getting screwed up somewhere.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[litton1]

This is my home PC. Perhaps it clashes with my admin user account... It was that long ago when I set up this PC, I just dunno.. I will drop the user and use a different user name for the SQL Server. Thanks, all of you for your help with this problem I really appreciate it!

If that doesn’t work I will try ptheriault's idea,

Age is a consequence of experience

 

[litton1]

Ok Guys! Thinking caps on ...plz cos my database is soooo secure I cannot get in?
I have run

CREATE LOGIN asmtony1 with PASSWORD = 'Asmt0ny'
use Forum
CREATE USER asmtony1 FOR LOGIN asmtony1 WITH DEFAULT_SCHEMA = dbo
go
--give user read and write access
EXEC sp_addrolemember 'db_datareader', 'asmtony1'
GO
EXEC sp_addrolemember 'db_datawriter', 'asmtony1'
GO

I still get. Not a trusted user? Could it be that I have set it up to only accepted a certain user. Anything’s possible here. Take nothing for granted. Thanks again!

Age is a consequence of experience

 

[gmmastros]

Is your server set to 'Windows Authentication Mode'?

If you want to log in with a SQL Account, then you need to change the authentication mode to 'SQL Server and Windows Authentication mode'. Here's how...

Open 'Microsoft SQL Server management studio'
Right click the server in the 'object explorer' window.
click the 'security' tab.
Check the 'server authentication' section.

If the authentication mode is set to 'Windows Authentication mode', then change it to 'SQL Server and Windows Authentication mode'.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

When you installed SQL, what mode for authentication did you select? Mixed, SQL or Windows. It should be mixed or SQL for what you are trying.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[litton1]

I don’t actually know? Is it changeable if I picked windows authentication? Should i reinstall and pick...SQL?

Age is a consequence of experience