db permission problem

Question

Post reply

db permission problem

spectra

SSCarpal Tunnel

Points: 4120
More actions
August 30, 2021 at 1:46 pm

#3925081
I have installed SQL Express 2019 and SSMS in a Windows System.
I am using Windows Authentication to log into DB server.
I created a database "MyDB"
Now another user logs into same DB server by his Windows Authentication. However he is unable to access DB
Error: The Database MyDB is not accessible.

How do I give DB access to other user ?

I tried this :
ALTER LOGIN [someOtherUser] WITH DEFAULT_DATABASE = [MyDB]
Error : Cannot alter the login 'someOtherUser', because it does not exist or you do not have permission.
- This topic was modified 3 years, 2 months ago by spectra.
- This topic was modified 3 years, 2 months ago by spectra.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27054 More actions · Answer 1

You are attempting to add a user to a database that does not have a login to the server.

There are 2 steps. The first is to add the LOGIN to the SERVER.

The second is to add the USER to the DATABASE.

You missed the first step

USE [master]
GO
CREATE LOGIN [TheLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[YourDatabase]
GO

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 2

You missed the first step

USE [master]
GO
CREATE LOGIN [TheLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[YourDatabase]
GO

User is unable to login now with Windows Authentication after doing this .

login failed . error 4064

This reply was modified 3 years, 2 months ago by spectra.
This reply was modified 3 years, 2 months ago by spectra.

Michael L John One Orange Chip Points: 27054 More actions · Answer 3

spectra wrote:

You missed the first step

USE [master]
GO
CREATE LOGIN [TheLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[YourDatabase]
GO
User is unable to login now with Windows Authentication after doing this .
login failed . error 4064

Did you actually spend any time googling what that error may indicate? There are two possible solutions to fix this.

One is to change the options of how the user connects, the other is to change permissions.

I'll let you do some work on figuring out the details.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 4

One is to change the options of how the user connects,

User will use Windows Authentication login in SSMS. No change here.

the other is to change permissions.

What do you mean by this ?

I already did this

ALTER LOGIN [TheLogin] WITH DEFAULT_DATABASE = [YourDatabase]

I dont understand what you mean here ?

This reply was modified 3 years, 2 months ago by spectra.

Michael L John One Orange Chip Points: 27054 More actions · Answer 5

spectra wrote:

One is to change the options of how the user connects,
User will use Windows Authentication login in SSMS. No change here.

What are the DEFAULT options when connecting to a server with SSMS?

Figure that out, and you should be able to figure it out.

spectra wrote:

the other is to change permissions.
What do you mean by this ?

See above, the DEFAULT connection options. In the absence of changing the options when connecting, you will have to change permissions.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

Setting a default database is not permissions. It just tells SQL which database to set the initial connection for that user.

spectra wrote:

I already did this
ALTER LOGIN [TheLogin] WITH DEFAULT_DATABASE = [YourDatabase]

This reply was modified 3 years, 2 months ago by homebrew01.
This reply was modified 3 years, 2 months ago by homebrew01.

Michael L John One Orange Chip Points: 27054 More actions · Answer 7

homebrew01 wrote:

Setting a default database is not permissions. It just tells SQL which database to set the initial connection for that user.

Correct. But if you won’t or can’t change the defaults, you will need to change permissions

This reply was modified 3 years, 2 months ago by Michael L John.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

Spectra, you can use the GUI to set permissions, and use the "SCRIPT to Query window" option and SQL will put the commands in a new query screen so you can see them.

You may find that helpful.

This reply was modified 3 years, 2 months ago by homebrew01.

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 9

>What are the DEFAULT options when connecting to a server with SSMS?

I'm not sure which DEFAULT options you are talking about.

I get many lists ......are you talking about these ?

SQL Authentication // I don't use it . I installed as Windows Authentication

2. Windows Authentication

3. Azure AD with MFA

4. Azure AD Password

5. Azure AD Integrated.

However I am using only Windows Authentication to login in SSMS.

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 10

Spectra, you can use the GUI to set permissions, and use the "SCRIPT to Query window" option and SQL will put the commands in a new query screen so you can see them.

I'll try this. But I have already executed

USE [master]

GO

CREATE LOGIN [TheLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[YourDB]

GO

and

ALTER LOGIN [TheLogin] WITH DEFAULT_DATABASE = [YourDB]

Do I require to rollback anything here first ?

also you said

you can use the GUI to set permissions,

I did a right click on database > property>permission

How do you set permission here ?

I did some effort like this > search TheLogin for user/roles in the pop up to add. But there was no match.

homebrew01 SSC Guru Points: 55548 More actions · Answer 11

Go To

SERVER ---> Security ---> Logins

You may need to hit "Refresh" for your new login to appear.

This reply was modified 3 years, 2 months ago by homebrew01.
This reply was modified 3 years, 2 months ago by homebrew01.

Michael L John One Orange Chip Points: 27054 More actions · Answer 12

No. Neither one of you are even close.

Screenshot 2021-08-30 134314

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 13

No. Neither one of you are even close.

Yes. I can go there as per the screenshot.

I don't see anything significant there. Its just couple of tabs e.g Connection properties , Always Encrypted , Additional Connection Parameters.

What is to be done with this ?

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 14

You may need to hit "Refresh" for your new login to appear.

That already showing there !

Error comes even after that.