Archive

Archive for February, 2017

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: , ,

Objects and Models Combine Photography & Digital

February 23, 2017 Leave a comment

i-cant-jump-any-higher-in-this-dress

As I learn more about lighting and object placement in a 3D world, I’m drawn to the parallels that I find to actual settings on my cameras. I shoot with both 5D and 7D cameras.  And studying the physics of lighting in the 3D world I’ve been able to duplicate my cameras settings that I would have used in the real world.  And it makes a dramatic impact when rendering images.

The same rules have been applying to 3 point light setup and adjustments that I would use on a live shoot.

 

 

Digital Art 3D Learning Is A Process

February 22, 2017 Leave a comment

I knew absolutely nothing about 3d worlds in December 2016. As I have taken a deep dive in learning the technology, I’ve been having frustrating issues with Blender 2.78 and that learning curve although steep has been great for my creativity. I’ve added 3dsmax, Maya, Mud box, Motion builder from Autodesk and Daz3d, Carrara Pro 8.5 from DAS Studios to the tool kit.  I’m also creating virtual worlds with unity and the unreal game environments. Photoshop. Yup can’t forget tools from Adobe in the mix.

A bonus for me as I’ve been a programmer for over 40 years, the majority of these new tools use python and PostgreSQL and that was ok for me. If you have kids and if they play Roblox there is the LUA programming language as well. And, that’s what really propelled me into learning about 3d worlds and art. The kids wanted me to teach them scripting and programming so they create their own items. 8, 10, 14 and 61 J all learning programming for the future.  3d and eventually VR. Modeling for 3d printers can be done with the tools I now have.

In a couple of weeks, will be sharing the scripts and mini programs I’ve been creating to assist with my use of these tools. I have a huge asset library and by adding my own SQL I can quickly lookup and see what application I’ve used an asset for etc. Licensing for game use was the primary concern for creating and tracking props and assets.

As the title partially states “Learning Is a Process”, I’d say yes it is! So stay tuned for more creations and info on my 3d journey.

summer-is-out-of-the-world