How to FIX: Manage Orphaned users in SQL SERVER with a simple script

Using below scripts

 

  • Fixes all orphaned users in the current database if a login exists
  • If a login does not exist in thesql server instance
    •  User will be removed  — @fix = 0
      • If a user is a schema owner then the schema is transferred to DBO and then user is removed from DB
    •  or A login will be created  — @fix = 1

 


DECLARE @t TABLE (

id INT IDENTITY(1, 1)

,username SYSNAME

,usersid VARBINARY(85)

);

DECLARE @username SYSNAME = ;

DECLARE @loopid INT = 0D

ECLARE @DeleteCmd NVARCHAR(1000) = 

DECLARE @fix BIT = 0; — if it is set 0 then all the orphans will be removed 

— if its is set 1 orphans will be created and mapped to the user with a default passwordINSERT INTO @t (

username

,usersid

)

EXEC sp_change_users_login @Action = ‘Report’;SET @loopid = @@IDENTITY;WHILE @loopid > 0

BEGIN

SET @username = (

SELECT username

FROM @t

WHERE id = @loopid

)

IF EXISTS (

SELECT 1

FROM master.dbo.syslogins

WHERE NAME = @username

)

BEGIN

EXEC sp_change_users_login @Action = ‘update_one’

,@UserNamePattern = @username

,@LoginName = @username;

PRINT CONVERT(VARCHAR, @username) + ‘User updated succesfully ‘

END

ELSE

BEGIN

IF @fix = 0

BEGIN

SELECT @DeleteCmd = @DeleteCmd + (

CASE

WHEN s.NAME IS NULL

THEN 

ELSE ‘ALTER AUTHORIZATION ON SCHEMA::’ + ‘[‘ + s.NAME + ‘] TO dbo’ + ‘;’

END

) + ‘DROP USER ‘ + ‘[‘ + dp.NAME + ‘]’ + ‘;’

FROM sys.database_principals dp

LEFT OUTER JOIN sys.schemas s ON s.principal_id = dp.principal_id

WHERE type IN (

‘s’

,‘G’

,‘U’

)

AND dp.principal_id > 4

AND dp.NAME = @username

PRINT CONVERT(VARCHAR, @username) + ‘User Removed succesfully ‘

END

ELSE

BEGIN

EXEC sp_change_users_login ‘Auto_Fix’

,@username

,NULL

,‘Auto_Fix_Pass’;

PRINT CONVERT(VARCHAR, @username) + ‘ User has added to sql server with password Auto_Fix_Pass succesfully ‘

END

END

SET @loopid = @loopid  1;

ENDPRINT @DeleteCmdEXEC sp_executesql @DeleteCmd

— Print un resolved db accounts EXEC sp_change_users_login @Action = ‘Report’;

How to fix: Access to the remote server is denied because the current security context is not trusted SQL Server

You are seeing this error because you are trying to impersonate or  switching the execution context using EXECUTE AS clause and Executing a RPC on a linked server

 

LInked server

 

If you have a procedure on Server X

USE ServerXDB

Create procedure proc

execute as ‘TESTUSER’

Execute ServerY.TestDB.dbo.test

Select * from [ServerY] .TestDb.dbo.testtable

To Fix this you have two options
  1. Enable Trusty worthy on ServerXDB database on Serverx
  2. Remove the execute as clause in the procedure and grant execute privileges to user we used to set up the linked server

 

you can also try by signing a certificate on the proc procedure but I havent tried it

One of the best SQL Server Index Internals: A Deep Dive

its from Tim Chapman, one of the best index internal presentation

I will try to highlight the things he talk through this presentation

Indexes

  • Clustered vs Non clustered and filtered indexes
  • Non clustered indexes and include column effect on index structure
  • Heap
  • Heap vs Clustered
  • Various DMV utilization to understand index internals
  • How to detect fragmentation
  • Logical fragmentation and effects on query perfromance
  • Page splits , Page density, page fullness

Statistics

  • How to view statistics
  • Effect of statistics on query performance
  • effect of stats from index rebuild and reorg

Log usage

  • Index rebuild vs reorg
  • Log usage for Index rebuilds and reorganize

Over all must watch

 

Sql server datacollector issue specified @collector_type_uid is not valid in this data warehouse

agent error collector upload error

Executing the query “{call [core].[sp_create_snapshot](?, ?, ?, ?, ?, ?…” failed with the following error: “The specified @collector_type_uid (14AF3C12-38E6-4155-BD29-F33E7966BA23) is not valid in this data warehouse.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

if we look sp_create_snapshot procedure in Management_dataware_house  it accepts six parameters  @collection_set_uid   ,@collector_type_uid   ,@machine_name  , @named_instance  ,  @log_id   ,  @snapshot_id

this procedure creates a snapshot in the management datawarehouse for a collection instance one parameter is Collecter_type_id

Note: Sql Server Data collector can collect data from Dynamic management views , SQL trace and Windows performance counters using TSQL and WMI query’s

We can get list of availanle data collector types by querying

SELECT

[collector_type_uid],[name]

,[parameter_schema]

,[parameter_formatter]

,[schema_collection]

,[collection_package_name]

,[collection_package_folderid]

,[upload_package_name]

,[upload_package_folderid]

,[is_system]

FROM [msdb].[dbo].[syscollector_collector_types_internal]

Collector types

insertinto [core].[supported_collector_types_internal](collector_type_uid)

values (’14AF3C12-38E6-4155-BD29-F33E7966BA23′)

 

collector suscces full

Data Cube: A Relational Aggregation Operator : Microsoft research paper

Very nice article on group by clause , how it works and what are its limitations and how can we overcome with a CUBE operator

Data analysis applications typically aggregate data across many dimensions looking for unusual patterns. The SQL aggregate functions and the GROUP BY operator produce zero-dimensional or 1-dimensional answers. Applications need the N-dimensional generalization of these operators. This paper defines that operator, called the data cube, or simply cube. The cube operator generalizes the histogram, cross-tabulation, drill-down, and sub-total constructs found in most report writers. The cube treats each of the N aggregation attributes as a dimension of N-space. The aggregate of a particular set of attribute values is a point in this space. The set of points form an N-dimensional cube. Super-aggregates are computed by aggregating the N-cube to lower dimensional spaces. Aggregation points are represented by an “infinite value”, ALL. For example, the point would represent the global sum of all items. Each ALL value actually represents the set of values contributing to that aggregation

Source :

http://research.microsoft.com/apps/pubs/default.aspx?id=69578

 

Download PDF file by clicking below link

MIcrosoft cube research

 

How to summarize your data using ROLLUP and CUBE in TSQL

We all know when reporiting any financial or sales information adding sub totals and grand totals to report makes it more effective but most offten develop0rs dont realize TSQL provides a effective way in doing this is using rollup and cube operators in group by clause.

So for this demo I am using Adventure works 2008 OLTP sample database

Three tables

  1. Production.Product
  2. Production.ProductCategory
  3. Production.ProductSubcategory

here is a simple querying joining three tables and grouping by product category and subcategory

select

pc.ProductCategoryID,

s.ProductSubcategoryID,

AVG(ListPrice)as Avglistprice,

MAX (ListPrice)as MAxlistprice,

MAX(StandardCost)as Maxstandardcost

from Production.Product p

inner join Production.ProductSubcategory s

on  p.ProductSubcategoryID = s.ProductSubcategoryID

inner join Production.ProductCategory pc

on  pc.ProductCategoryID = s.ProductCategoryID

group by pc.ProductCategoryID,s.ProductSubcategoryID

SAMPLE RESULT

You can see we are missing some things in the above result i.e  subtotals and grand totals

change group by cluase to include rollup operator like this

group by ROLLUP(pc.ProductCategoryID,s.ProductSubcategoryID)

trying running it with out errors

Result looks like this

CUBE is used as same way but it gives us a little different result , CUBE summarizes all the combinations of  columns in group by clause and it also gives us grandtotal which does not make sense for above example because each sub category exists once in each category.

you will ‘%_[^LIKE]%’ like

Like cluase allows you to match a character string found in a column to a specified pattern in the where clause

lets take an example to better understand its wild cards

How to use like in where clause

DECLARE@TTABLE (charcvarchar(20))

INSERTINTO@T(charc)

VALUES(‘Cat’)

,(‘hat’)

,(‘that’)

,(‘at’)

,(‘c’)

,(‘a’)

 

 

Percent % :

Underscore _ :

Square Brackets [] :

Caret:

 

 

 

 

 

 

 

watch out for nulls .. know ur environment

ANSI_NULLS is a user option in sql server , which is like a env variable when a user establishes a connection to sqlserver

ANSI_NULLS specifies the behavior of nulls when using equality and inequality comparision against null

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

as ANSI_NULLS is turned on , any query having comparing nulls will result in unknown result

Once we turn it off we will be able to query

 

 

 

Worst enemy to a developer (nolock) , Why shouldn’t you use

As I said before this is the exact same session that I learned from Randy Knight , when I saw his presentation I’ve decide not to use any where unless required especially in fianancial and critical decsion making reports , here we will not be going in to details of different isolation levels but we will be specifically focusing on  readuncommited isolation level or using nolock as tablehint , dirty reads and pagesplits

My first time at SQL saturday (#129 Rochester)

Hi,

Unfortunately I didn’t had enough time to update my experience at Sql Saturday #129 Rochester but here it is

First as I live in Pennsylvania so it’s a 3 hour journey to the north I packed my bags and reserved a hotel near Rochester institute of Technology that’s where SQL Saturday was held

I was very excited, normally I like travelling so anyway , I started here on Friday evening and reached there around 11 O’clock and had a good sleep and woke up around 7 O clock got my things together took a snapshot of the map and was at RIT by 8 30 , registration supposed to close around 8 45 , as soon as I got out of parking I met a guy who said he was also heading to SQL Saturday and this was his first time too and then we went inside took our badges and schedule, as I already made my schedule using schedule manager in sql saturday , so I pretty much knew what I am gona listen on that day , atmosphere was good and there were quite a few vendors for that event like redgate, confio…. for marketing there DBMS tools and other stuff where we can have a chance to get some freebies like eBooks, and books

 

Here  is my schedule for that day

What to look for Execution plans

Grant Fitchey

http://www.scarydba.com

also a author of a book called SQL Server Execution plans , free eBook available to download on redgate

My first session , I was very excited because I was reading his book for a while (First chapter) lol, any way his presentation was pretty bold concentrating more on the first operator (Select insert update delete) and important properties of first operator

Compile time

Compile CPU

Cache plan size

Estimated number of rows

Estimated Subtree cost

Reason for early termination

Optimization Level

Parameters

and we also looked at why seek or scan , and how to suppress key lookup , on the whole pretty good learned some new things

Next session

Top Tips writing better TSQL queries (beginer level)

How to format tsql for better readability

Naming standards (procedures(get update delete), naming variables)

Error handling

Unnecessary Explicit data conversion

Improper use of functions, query hints

On the whole pretty good topic to discuss , I’ve added a link to download his presentation

Top Tips for Better TSQL

Getting started with MDX

Willima E. Pearson III

Believe me most toughest thing to understand in SQL BI stack is MDX (Multi dimensional Expression language), its was the best presentation I’ve seen on the MDXeasy to understand for beginners , and we went through some beginner stuff like Dimensions, facts, members , SET, TUPLE . I havent had a chance to email him for asking his presentation once I get it, I will upload here so hold on and other two sessions you can see is from Randy Knight , well known sql guru I will post his session information in an another post because those are worth spending more time.

so that’s all from the sessions , at the end of the day they do this raffle drawing and I did not won anything and we finished our sql saturday there, and I made some new friends we went to movie it was fun , learned lot of stuff and am looking forward to attend the next sql saturday at Philadelphia soon.

Thank you