• Register
0 votes
259 views

Problem :

I want to access my hosting server’s database through SQL Server Management Studio, everything is fine but when I use the following command use myDatabase it gives me following error:

The server principal "****" is not able to access the database "****" under the current security context.

Can anyone give me solution on my problem?

6 5 3
6,930 points

Please log in or register to answer this question.

2 Answers

0 votes

Solution :

We had faced the same issue while deploying our report to SSRS in our PROD environment. We found that the problem could be reproduced with a event the “use ” statement. And the solution which we found is to resync the user's GUID account reference with the database in question we used "sp_change_users_login" after restoring a db. Please find below a cursor driven script to resync all accounts:

USE <your database name>
GO
-------- Reseting SQL user account guids --------------------
DECLARE @UserName nvarchar(255) 
DECLARE orphanuser_cur cursor for 
      SELECT UserName = su.name 
      FROM sysusers su
      JOIN sys.server_principals sp ON sp.name = su.name
      WHERE issqluser = 1 AND
            (su.sid IS NOT NULL AND su.sid <> 0x0) AND
            suser_sname(su.sid) is null 
      ORDER BY su.name 
OPEN orphanuser_cur 
FETCH NEXT FROM orphanuser_cur INTO @UserName 
WHILE (@@fetch_status = 0)
BEGIN 
--PRINTING @UserName + ' user name being resynced' 
exec sp_change_users_login 'Update_one', @UserName, @UserName 
FETCH NEXT FROM orphanuser_cur INTO @UserName 
END 
CLOSE orphanuser_cur 
DEALLOCATE orphanuser_cur
9 7 4
38,600 points
0 votes

Solution:

Check to view in case your user is mapped to the DB you are attempting to log into.

We had the similar error deploying a report to SSRS in our PROD environment. It was constructed the issue could even be reproduced with a “use ” statement. The resolution was to re-sync the user's GUID account reference with the database in question (for example employing "sp_change_users_login" like you would after restoring a db). A stock (cursor driven) script to re-sync all accounts is attached:

USE <your database>
GO

-------- Reset SQL user account guids ---------------------
DECLARE @UserName nvarchar(255) 
DECLARE orphanuser_cur cursor for 
      SELECT UserName = su.name 
      FROM sysusers su
      JOIN sys.server_principals sp ON sp.name = su.name
      WHERE issqluser = 1 AND
            (su.sid IS NOT NULL AND su.sid <> 0x0) AND
            suser_sname(su.sid) is null 
      ORDER BY su.name 

OPEN orphanuser_cur 
FETCH NEXT FROM orphanuser_cur INTO @UserName 

WHILE (@@fetch_status = 0)
BEGIN 
--PRINT @UserName + ' user name being resynced' 
exec sp_change_users_login 'Update_one', @UserName, @UserName 
FETCH NEXT FROM orphanuser_cur INTO @UserName 
END 

CLOSE orphanuser_cur 
DEALLOCATE orphanuser_cur

 The fact that I had forgotten which specific database I was targeting my connection to. I was managing the standard SQL Server connection window to enter the credentials:

I had to check the Connection Properties tab to accomplish that I was selecting the right database to connect to. I had unexpectedly left the Connect to database option here fixed to a selection from a former session. This is why I was unable to connect to the database I thought I was attempting to connect to.

Note that you require to click the Options >> button in order for the Connection Properties and other tabs to show up.

This performed for me:

use <Database>
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='<userLogin>',@LoginName='<userLogin>';

The problem can be noticed with:

SELECT sid FROM sys.sysusers WHERE name = '<userLogin>'
SELECT sid FROM sys.syslogins WHERE name = '<userLogin>';

SQL Logins are defined at the server level, and should be mapped to Users in particular databases.

In SSMS object explorer, under the server you want to modify, expand Security > Logins, then double-click the appropriate user which will bring up the "Login Properties" dialog.

Choose user Mapping, which will display all databases on the server, with the ones having an subsisting mapping chosen. From here you can choose additional databases (and certain to choose which roles in each database that user must belong to), then click OK to include the mappings.

These mappings can become disconnected after a restore or same operation. In this instance, the user may still subsist in the database however is not really mapped to a login. In case that occurs, you can run the following to restore the login:

USE {database};
ALTER USER {user} WITH login = {login}

You can further delete the DB user and recreate it from the Login Properties dialog, however any role memberships or other settings would require to be recreated.

In my instance, the message was happened by a synonym which inadvertently added the database name in the "object name". At the time I restored the database under a new name, the synonym still pointed to the old DB name. Because the user did not have permissions in the old DB, the message appeared. To solve, I dropped and recreated the synonym without qualifying the object name with the database name:

    USE [new_db]
GO

/****** Object:  Synonym [dbo].[synTable]    Script Date: 10/15/2015 9:45:01 AM ******/
DROP SYNONYM [dbo].[synTable]
GO

/****** Object:  Synonym [dbo].[synTable]    Script Date: 10/15/2015 9:45:01 AM ******/
CREATE SYNONYM [dbo].[synTable] FOR [dbo].[tTheRealTable]
GO

 

10 6 4
31,120 points

Related questions

0 votes
1 answer 161 views
161 views
Problem : I am facing following error with the below query in SQL Server 2012. An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN'. SQL Query: CREATE FUNCTION [dbo].[GetMYPMResources](@UserResourceNo ... JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo WHERE t.TaskManagerNo = @UserResourceNo OR t.AlternateTaskManagerNo = @UserResourceNo ) RETURN END
asked Dec 4, 2019 alecxe 7.5k points
0 votes
1 answer 2 views
2 views
Problem: sql server management studio export to csv.
asked 1 day ago Dan phillip 4.7k points
0 votes
1 answer 25 views
25 views
Problem: system.componentmodel.win32exception: the network path was not found.
asked Feb 12 Shovo210 13.3k points
0 votes
1 answer 4 views
4 views
Problem: How to select a partial of a table for exporting using SQL? How can I do that? Thanks
asked Mar 9 ummesalma 24.5k points
0 votes
1 answer 8 views
8 views
Problem: I am struggling to get a SQL Server connection from machine A to machine B which is running the SQL Server. I would be happy to generate more debug info, just tell me what you need.
asked Mar 20 ummesalma 24.5k points
1 vote
1 answer 160 views
160 views
Problem : My task is to send email with the file attachments also this attachment must come from the image type data field. If I try to run the below code in both the query window and the stored procedure but my email was not sent and in the query window I can only see ... = 1,     @query_no_truncate = 1; I need to send email but still unable to do so. I am looking for help in fixing the issue.
asked May 6, 2020 stewart 4k points
0 votes
1 answer 324 views
324 views
Problem : I don't understand why I am unable to insert this. I can't spot any problem. The error message is as below : Conversion failed when converting from a character string to uniqueidentifier. A GUIDs are the ... Server 2012 The columns are as below id        uniqueidentifier, myid      uniqueidentifier, friendid  uniqueidentifier, time1     datetime nullable, time2     datetime nullable
asked Jan 17, 2020 jwilliam 3.9k points
0 votes
1 answer 2 views
2 views
Problem: Access control is not enabled for the database mongodb.Do you have any ideas about how I would be able to manage this problem? Thank you a lot!
asked Mar 31 zayed1 30.9k points
0 votes
1 answer 19 views