Thursday 24 October 2013

Find Orphan SQL Users and Fix using ALTER USER

For years i have been using syslogins and sysusers tables in a cursor to find orphan users and sp_change_users_login to fix orphan users in SQL Server database.

With sp_change_users_login depreciated from SQL 2014 and with new system objects post SQL server 2005 I thought i would be a good time to post the new version of the TSQL i follow.
I have 2 versions of query to find Orphan users in a SQL database,

Version 1:-
 
WITH login_CTE (name, type, sid)
As
(
Select name, type, sid from sys.database_principals where type = 'S' AND name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA','sys')
)
select a.*,'ALTER USER '+a.name+' WITH LOGIN ='+a.name As Fixusers from login_CTE a
LEFT JOIN sys.server_principals b ON a.sid = b.sid
where b.name IS NULL

Version 2:-

Note:- This version had some issues with databases with non standard collation to master database, So I have used the COLLATE statment to match the collation. 

WITH login_CTE (name, type, sid)
As
(
Select name, type, sid from sys.database_principals where type = 'S'
)
select *,'ALTER USER '+a.name+' WITH LOGIN ='+a.name As Fixusers from sys.server_principals a JOIN login_CTE b ON a.sid <> b.sid and a.name = b.name COLLATE Latin1_General_CI_AS
GO

Both versions will will have a column with name Fixusers with the ALTER USER statement instead of sp_change_users_login to fix the orphan users. Just copy the column and execute the query to fix all the orphan users in the database.

Eg: ALTER USER TestLogin1 WITH LOGIN =TestLogin1

Note:- This query is database specific and needs to executed in all databases required to be fixed.

Friday 11 October 2013

Store Perfom Data Collector data to SQL Server


As a DBA we frequently run perfmon on our servers to monitor performance of the SQL Servers, In this section I am going to write how to schedule a perfmon data collector to collect the perfmon data to a SQL server destination.
 
Step 1:-
Open Perfmon from the SQL server you want to collect the counters, Perfmon can also be collected remotely but i ave used local server in this exercise.
 


 

 


 

Step2:-
Right Click Data Collector Set->User Defined -> New -> Data Collector set

 


Step 3:-
Select a name for the collector and chose manual configuration as below


 Step 4:-
Choose Create data logs->Performance Counter

 


 Step 5:-
 Choose the Interval in which the data needs to be collected and Click Add to select the counters 


 Select the count you wish to monitor and Click OK


Step 6:- 
Click Next, Don't worry about the Root directory at this point


 

Step 7:-
Choose the  account in which the collector jobs needs to run, preferably the account which has access to SQL server and WMI and Finish the configuration

 


 

Now you will be able to see the Data Collector set up in the Perfmon



 

Step 8:-
Next step is to create a ODBC for SQL server where the data needs to be stored, Go to Start->Administrative Tools->ODBC Data Source and choose System DSN tab.

 




 

Step 9:-
Click Add to create a new data source and select SQL Server data source, Please do not choose the Native client data sources, there is a known error with the Native client accessing Permon SQL server data and usually error out as "Call to SQLExecDirect failed with %1" when you start the data sets so make sure you choose SQL Server and proceed to create the DSN.

 


 


 Choose the database to which the Perfmon data needs to be stored as default database.


 


 

 

Step 10:-
Now go back to Permon data Set, go to you collector set "SQL Server Usage" in my case
and right click on DataCollector01 and properties.



 




 

Step 11:-
In the Properties screen choose the format as "SQL" which should enable your Data Source Name drop down box, Check if the system DSN you created is available in the dropdown box and choose it as the data source and Click apply and OK.


 


 Step 12:-
Now all the setup has been completed just right click on your collector set and click start which should start collecting the permon data to SQL Server.

 



To Verify the SQL data go to the SQL database to which the data is collected and execute the following query,
 
 
select
a.counterid,CounterDateTime,b.ObjectName,b.CounterName,b.InstanceName,DefaultScale,CounterValue from CounterData a
JOIN
CounterDetails b ON a.CounterID = b.CounterID
 
You can make this database as DW and use BI tools to create reports to SSRS and or any other reporting tools