For new Login unable to access Database

כל התגובות

• יום שישי 02 מרץ 2012 10:48

   

   
  

  0

  Hallo Maddy,

  make sure that no other "user name" exists in the database with the name maddy.
  You can check orphaned users by using the following script:

  USE [database]
  GO

  EXEC sp_change_users_login 'report'

  To understand the differences between login and users of a database see
  http://msdn.microsoft.com/en-us/library/bb510418.aspx

  To get detailled information concerning sp_change_users_login see:
  http://msdn.microsoft.com/en-us/library/ms174378.aspx

  ---

  Uwe Ricken
  
  MCITP Database Administrator 2005
  MCITP Database Administrator 2008
  MCITP Microsoft SQL Server 2008, Database Development
  
  db Berater GmbH
  http://www-db-berater.de

  • סומן כתשובה על-ידי Iric WenModerator יום שני 12 מרץ 2012 09:19
  •  
  • תגובה
  • ציטוט

   
• יום שישי 02 מרץ 2012 11:01

   

   
  

  0

  Ricken,

  i excuted EXEC sp_change_users_login 'report' on server and i dont maddy as orphan user and there is no other user name is like Maddy, what should i do next

  • תגובה
  • ציטוט

   
• שבת 03 מרץ 2012 21:34

   

   
  

  0

  Maddy,

  please take care that you run the sp in the database where you want to add the user!
  I suppose that you did run the proc in the master database.

  ---

  Uwe Ricken
  
  MCITP Database Administrator 2005
  MCITP Database Administrator 2008
  MCITP Microsoft SQL Server 2008, Database Development
  
  db Berater GmbH
  http://www-db-berater.de

  • תגובה
  • ציטוט

   
• יום ראשון 04 מרץ 2012 09:58

  מנחה דיון

   

   
  

  0

  Hi,

  Please make sure whether that user exists in the database or not, you can run the following query:

  USE <db_name>;
  GO
  SELECT name, type_desc
  FROM sys.database_principals;
  GO

  
  

  ---

  Alex Feng | SQL Server DBA, ALIBABA.COM
  My Blog | MCTS: SQL Server 2008, Implementation and Maintenance
  Please remember to mark the replies as answers if they help and unmark them if they provide no help.

  • תגובה
  • ציטוט

   
• יום ראשון 04 מרץ 2012 17:01

   

   
  

  0

  Hi Alex,

  just as an information - your solution will not work because user names can be totaly different from the login name.
  Your query will not really give the requestor the needed information. You need to join the database_principals with server_principals.
  Therefore the best would be using sp_change_users_login!

  In general only the SId is the identifier between server_principal and database_principal.

  security: http://msdn.microsoft.com/en-us/library/ms181127.aspx
  server_principals: http://msdn.microsoft.com/en-us/library/ms188786.aspx
  database_principals: http://msdn.microsoft.com/en-us/library/ms187328.aspx

  ---

  Uwe Ricken
  
  MCITP Database Administrator 2005
  MCITP Database Administrator 2008
  MCITP Microsoft SQL Server 2008, Database Development
  
  db Berater GmbH
  http://www-db-berater.de

  • תגובה
  • ציטוט

   
• יום שני 05 מרץ 2012 06:47

  מנחה דיון

   

   
  

  0

  Hi Maddy,

  An add-in to Uwe, you can refer to this blog to troubleshoot your issue:

  SQL SERVER – FIX : Error 15023: User already exists in current database

  http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

  ---

  Best Regards,
  Iric
  Please remember to mark the replies as answers if they help and unmark them if they provide no help.

  • סומן כתשובה על-ידי Iric WenModerator יום שני 12 מרץ 2012 09:19
  •  
  • תגובה
  • ציטוט