RSS

Monthly Archives: April 2016

SQL GULF #3 RIYADH 2016

Welcomes you to the first SQL Server 2016 event in the Middle East at AlFaisal University on 23/4/2016

sql-gulf-3We are so glad to announce SQL Gulf #3 event , Riyadh 2016 which will be held at Al-Faisal University on 23th April where top notch MVP, MCM and MCA experts are coming from different countries worldwide Australia , US, UK and Germany to speak to you about SQL Server 2016 ONLY…! It is the first event ever in the middle east to reveal about the latest Microsoft SQL Server 2016 technologies and techniques ,the event is for both male and female ,come to register here ASAP http://waja.com.sa/SQLGulf3/

12924351_1141298549213799_4742778383078550246_n

 

Speakers 

Read the rest of this entry »

 
4 Comments

Posted by on April 9, 2016 in General topics

 

How to get orphaned login for all databases?

Following script gives you all user database users, that do not have a server login mapped or where database user name does not match server login name. This is follow up post to How get all users and their role mappings from all databases? I posted few days ago.

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output
GO

CREATE TABLE #Output (DatabaseName VARCHAR(255), UserLoginSID varbinary(128), ServerLoginName VARCHAR(255), DatabaseUserName VARCHAR(255), UserType VARCHAR(50))
GO

sp_msforeachdb '
INSERT INTO #Output
SELECT  ''?''
      , DP.sid
      , SP.name
      , DP.name
      , DP.type_desc
  FROM sys.database_principals DP
  LEFT JOIN sys.server_principals SP
    ON DP.sid = SP.sid
 WHERE DP.type_desc IN (''SQL_USER'',''WINDOWS_USER'')
   AND DP.sid IS NOT NULL'
GO

  SELECT *
    FROM #Output
   WHERE ServerLoginName IS NULL
      OR ServerLoginName <> DatabaseUserName
ORDER BY DatabaseName, ServerLoginName
GO

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on April 5, 2016 in General topics