14,494 questions
I think Olaf is correct your SQL Server is having currently Windows Authentication Mode you need to enable mixed more authentication. Post that a SQL Server service restart is required to make the change effective
Grant releveant access to a user in SQL Server
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(201.6, 80%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EK%3C/text%3E%3C/svg%3E)
Krish
81
Reputation points
Hi Everyone,
I am new to this area and i request you to suggest me to come out of my confusions.
I have a data base in sql server 2017 as db_test and a user user1. I need to grant permissions to this user. I tried below scripts and executed the scripts with no errors. But when i am trying to log in with this user , i am not able to connect to the server itself. What i am missing here ?
CREATE LOGIN user1 WITH PASSWORD = 'Pa$$word123'
CREATE USER user1
EXEC sp_addrolemember 'db_datareader', 'user1'
GRANT CREATE SCHEMA TO user1
GRANT CREATE PROCEDURE TO user1
GRANT CREATE FUNCTION TO user1
Thanks in advance,
SQL Server Other
{count} votes
-
Olaf Helper 47,436 Reputation points2020-09-08T13:16:29.74+00:00 An which error message do you get when you try to logon using that SQL credentials?
-
Krish 81 Reputation points
2020-09-08T13:33:50.213+00:00 Error as below :
TITLE: Connect to Server
Cannot connect to CGSCLX060373106.
ADDITIONAL INFORMATION:
Login failed for user 'user1'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
But the Password is not wrong.
-
Olaf Helper 47,436 Reputation points
2020-09-08T13:39:23.897+00:00 Is SQL Server running in Mixed mode = "SQL and Windows auth"; that's mandatory for SQL login.
Otherwise lookup SQL Server ErrorLog for more details about the logon error. -
Krish 81 Reputation points
2020-09-08T16:00:20.913+00:00 Thanks a lot Olaf. updated the mode and below is the error after that.
Cannot connect to CGSCLX060373106.
ADDITIONAL INFORMATION:
Cannot open user default database. Login failed.
Login failed for user 'user1'. (Microsoft SQL Server, Error: 4064)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476
-
Shashank Singh 6,251 Reputation points2020-09-08T17:26:38.183+00:00 Could you check if this is helpful.
https://blog.sqlauthority.com/2008/11/04/sql-server-fix-error-4064-cannot-open-user-default-database-login-failed-login-failed-for-user/ -
Krish 81 Reputation points
2020-09-09T03:39:32.53+00:00 Hi Shashank,
i am able to access the server now , but not able to access the data base with in the server. Do we have any scripts to enable this also please?
TITLE: Microsoft SQL Server Management Studio
The database xxx is not accessible. (ObjectExplorer)
-
Shashank Singh 6,251 Reputation points
2020-09-09T05:31:14.54+00:00 You provided DB_Datareader access to the login to only one database it seems you would have to provide it to all database you want to read. Simply run your command you posted in question in database you want access.
USE db_name; ---the db in which u want access GO CREATE USER user1 FOR LOGIN user1; GO EXEC sp_addrolemember 'db_datareader', 'user1'Or see This Link
Sign in to comment
4 answers
Sort by: Most helpful
-
-
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff2020-09-09T03:37:58.16+00:00 Hi @kirankumar-3620,
> Cannot open user default database. Login failed. Login failed for user 'user1'. (Microsoft SQL Server, Error: 4064)
According to the error message, please try to change the user's default database to a database that's currently available for a connection. Please refer to below blogs to get more information.
Error message when you connect to an instance of SQL Server: "Cannot open user default database"
SQL SERVER – Fix : Error: 4064 – Cannot open user default database. Login failed. Login failed for userIf It is not work, please let me know.
Best regards,
Cathy
If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. -
Olaf Helper 47,436 Reputation points
2020-09-09T06:31:23.427+00:00 Additional see Change server authentication mode
-
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
2020-09-09T07:49:57.803+00:00 Hi @kirankumar-3620,
> The database xxx is not accessible. (ObjectExplorer)
You can also resolve this issue from SSMS UI as below screenshot. Right click user1 login > Properties > User Mapping
Best regards,
Cathy
If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.-
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
2020-09-14T06:11:58.22+00:00 Hi @kirankumar-3620,
Any update for this thread? Did you resolve your issue? If the responses helped, do "Accept Answer". If you still have any issue, please feel free to let us know. Thanks.
Best regards,
Cathy
Sign in to comment -