db permission problem
-
Ok,
spectra wrote: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 ?
Well, as you say, there is nothing significant there??? Again, have you actually tried to figure out what all of those things mean?
When I asked about DEFAULTS, did anything on that page stick out?
Change the database. The default database SSMS is attempting to connect to is master. Your user has no rights to master. So you need to either set their default to the master database, or when connecting they need to change to the database that they have access to.
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 wrote:
You may need to hit "Refresh" for your new login to appear.
That already showing there !
Error comes even after that.
Have you given the user any type of permission to any databases ?
-
Change the database.
It did not work....same error
-
Have you given the user any type of permission to any databases ?
How do you do that ?
other than those two sql query execution , I did not do anything else.
-
Try this link
https://lmgtfy.app/?q=login+failed+.+error+4064
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 wrote:
other than those two sql query execution , I did not do anything else.
The login just gives access to a connection.
After you create the login you have to give database permission(s). You need to decide what level of permission(s) to give.
Plenty of documents already posted on the internet. Better than me trying to write from scratch what is already better explained by others.
-
Try this link
Okay. But is there anything I missed on your solution ? I followed the same suggestion.
I checked this also
-
Access is given in multiple parts.
Logins and Users and permissions
logins give access to the server
users give access to the database(s)
permissions will Be at a role or individual securable option.
so what have you do so far.
looking at the thread you have only created a login, not a users.
lookuo the syntax for “CREATE USER” and create the user in the database in question, then you can look at granting permissions after.
-
lookuo the syntax for “CREATE USER” and create the user in the database in question, then you can look at granting permissions after.
See this is what I am doing ...
I log into SSMS using my windows "domain userid" in Windows Authentication mode and then I create database , tables , stored proc etc // I did not use any "create user" here !
Now I want another windows "domain user" to have access to the database which I created but issue is that another "domain user" is not able to access the database that I created. He is the getting the error which I posted.
do you still want to create new user !
-
Your login will probably have sysadmin rights as you are the one who installed SQL, as such you are essentially "God" on the server, nothing can stop you, you don't need a user mapping.
If you want to give other people access you need to do it based on what you want them to do.
If they only need to manage server level things, then they get a LOGIN and you grant them access to the server roles.
If they need to manage database level things, then they get a LOGIN, then they get a USER in the database they need access to, then you grant them access to the database roles.
If you want to give them god rights where nothing can stop them then you give them a login and grant sysadmin.
You need to be doing things in least privileged access modes, so giving things like sysadmin and db_owner permissions is generally frowned upon and this you need to create permissions on a least damage possible basis.
Example here using my testing account.
All I have here is a LOGIN, it has no permissions apart from the ability to CONNECT
If I want this LOGIN to access the AdventureWorks2019 I *MUST* create a USER for the LOGIN in the database.
You can do that by ticking the box above
Or by the CREATE USER syntax
USE AdventureWorks2019
GO
CREATE USER [Ant] FOR LOGIN [Ant]After that you are into granting permissions as nessesary.
Lookup the built in roles for SERVER and DATABASE and assign the right permissions for the role the user needs to do.
Do not blindly give SYSADMIN or DB_OWNER unless you know the security risks involved.
-
by the way you explain things it really looks like you know nothing of SQL Server permissions as this particular case is the most basics of access to any user.
Please do take some time going through the manuals and do get back to us with further questions and also do google for sql server permissions.
see
https://www.mssqltips.com/sqlservertip/6828/sql-server-login-user-permissions-fn_my_permissions/
-
The Stairway series on this site is also a very good learning resource
https://qa.sqlservercentral.com/stairways/stairway-to-sql-server-security
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/ -
However I am using only Windows Authentication to login in SSMS.
-
doesn’t matter on the authentication type, the exact same principals of logins/users/permissions still apply.
Although for ease of management create AD groups and create the SQL based login/user off of the group.
Rather manage 1 group in SQL than 1000 individual users.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply