Powershell: The Begining

From the past 6 months or so ,I have been trying to learn powershell because it would help me simplify a lot of activities that I do as a Database Administrator. But I was never able to read more than a page about it. (no time……nah…am lying …it was due to lack of interest)

Last week I came across this website http://midnightdba.itbookworm.com ,Created by the MVP duo- Sean and Jen “alias” Midnight DBA.

I watched Sean’s webinar about Powershell for beginners and that was really good. That video regenerated interest in me about powershell.Now I am reading more about powershell.

From now on I will be blogging about the things I have learnt and will be learning in powershell.

Thank you,Sean and Jen for the wonderful blog posts.

Script for fixing orphan users

As a follow up to my previous post for fixing orphan users,Here is the script that will allow to fix orphan users in a database

Declare @sql varchar(5000)

select @sql = coalesce (@sql ,'') + 'ALTER USER '+ name + ' WITH LOGIN = '+ name + ';'
from sysusers
where issqluser = 1
and (sid is not null and sid <> 0x0)
and (len(sid) <= 16) and suser_sname(sid) is null order by name Print @sql

Exec @sql

Fixing Orphan Users

You all might already known that if we have orphan users in the Database then using the stored Proc sp_change_users_login we can map the user to a Sql Login.
But According to Books Online, this feature will depreciated in the future.

So you guys must be wondering ,then how are we going to fix Orphan user Problem.
Well Here is the Solution.

Alter User UserName
With Login = LoginName

Replace the userName and LoginName accordingly with the orphaned user
and the existing login.

The advantage of using this method over sp_change_users_login is that
ALTER USER WITH LOGIN supports both SQL Server and Windows
logins where as sp_change_users_login works only for SQL Server
logins


I have picked up this tip from Laurentis Blog

http://blogs.msdn.com/lcris/

His Blog is an excellent resource for topics related to Sql Server Security