db permission problem
-
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.
-
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]
GOMichael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
You missed the first step
USE [master]
GO
CREATE LOGIN [TheLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[YourDatabase]
GOUser is unable to login now with Windows Authentication after doing this .
login failed . error 4064
- spectra wrote:
You missed the first step
USE [master]
GO
CREATE LOGIN [TheLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[YourDatabase]
GOUser 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/ -
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 ?
- 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/ -
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]
- 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
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, 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.
-
>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, 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.
-
Go To
SERVER ---> Security ---> Logins
You may need to hit "Refresh" for your new login to appear.
-
No. Neither one of you are even close.
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/ -
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 ?
-
You may need to hit "Refresh" for your new login to appear.
That already showing there !
Error comes even after that.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply