Archive

Posts Tagged ‘#sqlserver’

Find database Schema Users

February 26, 2017 Leave a comment

I’ve imported and migrated a tremendous amount of databases into SQLServer versions 2012 and above. Finding and identifying old owners as they would be orphans now or users that should not have access can be made easier by running or fully automating an import workflow by adding this script to the process.

It is pretty basic in structure and uses #TempTable rather than @TempTable.

SELECT Mp.Type AS UserType, Mp.name AS  UserName , Mp.default_Schema_name AS UserSchema
INTO #ModifySchemaTemp
FROM sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
WHERE Mp.Type = ‘U’

I select from sys.database_role_members and self join with database_principlas against databse_principals selecting user type “U” into my #ModifySchemaTemp table.
A simple WHILE loop provides the core of the script.  For the amount of data I choose not to use CURSORS.

SELECT @cnt=COUNT(*) from #ModifySchemaTemp

WHILE(@cnt >0)
BEGIN
–Set Loop While Keys
SELECT @UserKey=UserName from #ModifySchemaTemp
SELECT @SchemaKey=UserSchema from #ModifySchemaTemp

–REST OF PROCESSING

END

Modify the script to suit your needs. As you see from the script rather than take action immediately which could be done I’ve choose to print to the screen for later documentation.

SET NOCOUNT ON

SELECT Mp.Type AS UserType, Mp.name AS  UserName , Mp.default_Schema_name AS UserSchema
INTO #ModifySchemaTemp
FROM sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
WHERE Mp.Type = ‘U’

— Check results if you want uncomment the following
–SELECT * FROM #ModifySchemaTemp

DECLARE @UserKey VARCHAR(50)
, @SchemaKey VARCHAR(80)
, @cnt INT
, @SchemaString VARCHAR(512)
, @UserString VARCHAR(512)

SELECT @cnt=COUNT(*) from #ModifySchemaTemp

WHILE(@cnt >0)
BEGIN
SELECT @UserKey=UserName from #ModifySchemaTemp
SELECT @SchemaKey=UserSchema from #ModifySchemaTemp

— Don’t delete the default schema for DBO
IF @SchemaKey <> ‘DBO’
BEGIN

SELECT @SchemaString = ‘IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N”’ + @SchemaKey + ”’)
DROP SCHEMA [‘+ @SchemaKey + ‘]
GO’
print @SchemaString

–DROP USER ACCOUNTS ONLY FROM NEWLY CREATED RESTORED DATABASE!
SELECT @UserString = ‘IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N”’+ @UserKey + ”’)
DROP USER [‘ + @UserKey + ‘]
GO’
PRINT @UserString

END
ELSE
BEGIN
–DROP USER ACCOUNTS ONLY FROM NEWLY CREATED RESTORED DATABASE!
SELECT @UserString = ‘IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N”’ + @UserKey + ”’)
DROP USER [‘ + @UserKey + ‘]
GO’

PRINT @UserString
END

–END

DELETE FROM #ModifySchemaTemp WHERE UserName=@UserKey
SELECT @cnt=COUNT(*) FROM #ModifySchemaTemp

END
DROP TABLE #ModifySchemaTemp

Categories: TSQL Tags: , ,

PowerShell – Out-GridView Headers

October 26, 2016 5 comments

I’ve liked the simplicty of PowerShell when using the Out-GridView against a data set when you want a quick sortable list.

If you use Out-GridView most likely you’re cutting and pasting into an Excel sheet

Out-GridView dosen’t allow you to copy the header row. But, a simple process will help

For example:

From example #4 on this page. https://technet.microsoft.com/en-us/library/hh849920.aspx

($A = Get-ChildItem -Path $pshome -Recurse) | Out-GridView

This displays the header bar but you’re unable to copy this header directly that I’ve not found.

outgridview1

CTRL+A and no grid headers selected.

outgridview2

Now create a simple object and function to run for storing header values:

$global:ReportHeaders = @()

function Report-Headers() {
 # There are a few methods for createing objects. Keeping this simple
 # to show the possibilities

# Sets up dummy object with headers for use in out-gridview
 $local:TestCollection = @()
 $TestProperties = new-Object Object
 add-Member -inputobject $TestProperties -membertype NoteProperty 
-Name "Mode" -Value ""
 add-Member -inputobject $TestProperties -membertype NoteProperty 
-Name "LastWriteTime" -Value ""
 add-Member -inputobject $TestProperties -membertype NoteProperty 
-Name "Length" -Value ""
 add-Member -InputObject $TestProperties -MemberType NoteProperty 
-Name "Name" -Value ""

$TestProperties.Mode = "Mode"
 $TestProperties.LastWriteTime = "Last Write Time"
 $TestProperties.Length = "Length"
 $TestProperties.Name = "Name"
 $local:TestCollection += $TestProperties
 $global:ReportHeaders = $local:TestCollection
 }

Report-Headers
 ($A = Get-ChildItem -Path $pshome -Recurse) | Out-GridView
 $global:ReportHeaders +=  $a
 $global:ReportHeaders | Out-GridView

Now there are simple headers to copy into excel or other tools.

outgridview3

Sample Excel:

outgridview4

Well, something very simple. If you’ve found a different way to do this please let me know.

Joseph