Archive for January 31, 2011

SharePoint Funnies

SharePoint Funnies

Post to Twitter

How to transfer the logins and the passwords between instances of SQL

When you need to create a development enviroment of a production SQL box and you have SQL logins to your databases you need to recreate them on your dev enviroment.  It takes way to long to recreate each user so just take the steps below and you should be done in 5 minutes.

Microsoft explains it simply enough from here http://support.microsoft.com/kb/918992/

Take the below code and run it against the instance of SQL you want to copy the logins from.


USE master

GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

  DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

  DECLARE @tempint int

  DECLARE @firstint int

  DECLARE @secondint int

  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

  SELECT @firstint = FLOOR(@tempint/16)

  SELECT @secondint = @tempint - (@firstint*16)

  SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

  SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

  DROP PROCEDURE sp_help_revlogin

GO

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary  varbinary (256)

DECLARE @PWD_string  varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr  varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

  PRINT 'No login(s) found.'

  CLOSE login_curs

  DEALLOCATE login_curs

  RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

  IF (@@fetch_status <> -2)

  BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

    END

    ELSE BEGIN -- SQL Server authentication

        -- obtain password and sid

            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state

        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

        END

        IF ( @is_expiration_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

        END

    END

    IF (@denylogin = 1)

    BEGIN -- login is denied access

      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN -- login exists but does not have access

      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

    END

    IF (@is_disabled = 1)

    BEGIN -- login is disabled

      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

    END

Then once that has completed run the following command in the same query window


EXEC sp_help_revlogin

SQL will create a script that you need to copy and then paste into a query on the other SQL instance and run.

Post to Twitter

How to reboot a remote Windows Server the right way

This is the scenario, you have updated a few items on a remote server and the machine requires a reboot. You say “Yes” to the reboot and windows close and you are left with a blank screen thinking the server is doing its thing. Minutes later you ping the machine it looks like it is back up and you cannot connect. You try to connect to the server via RDP without success; you try to connect via management console without success. You end up calling onsite support to power off the machine after an hour of waiting.

Here is a good way to avoid this in future.

Never click “Yes” to a reboot now option given after an installation, rather finish up everything and close all windows manually.

With Windows Server 2003 Right click on Computer in the Start menu and click Manage

Right Click Computer Management and click Properties

Click advanced tab and Settings under Startup and Recovery

Click Shut Down

Select Restart under Action and Always under Force Apps Closed. (Bear in mind you might lose data this way so ensure everything you want closed is closed and saved.)

With Windows Server 2008 you don’t have the option of doing this so open up Powershell and run the below oneliner script, you can do this on a remote machine as long as you have administrator rights on the server you are trying to reboot. You could also do this for Windows 2003!

(gwmi win32_operatingsystem -ComputerName MyServer).Win32Shutdown(6)

Change “MyServer” to the servername you want to reboot.

Post to Twitter

The importance of community involvement within the SharePoint space

Yesterday on a warm breezy summers day in Cape Town, South Africa a couple blokes from different companies that compete for the same clients in the same field with the same product got together for an ice cold beer to chat about work … after work. Why?

Well despite the fact that beer was involved, one of the main reasons this astonishing phenomenon occurs is because they are passionate about what they do. The ancient Greeks did not write obituaries; instead they asked only one question of a man/woman. Did he/she have passion? Without passion one has no desire to better oneself. Passion is the driving force behind inspiration, happiness, success and motivation.

A career in the SharePoint space provides every opportunity to ensure that the passion for your career never slips away. A flourishing global community welcomes any newcomer to the scene with open ears and twittering hearts. Local based Information Worker (IW) and SharePoint Saturday (SPS) events are a great way to meet and great likeminded professionals from your area to share ideas, concerns and information. Global events such as Best Practices Conference (BPC) and Microsoft SharePoint Conference (MSC) to name a few are where the best and greatest SharePoint minds get together annually. And let’s not to forget a SharePint just to get together and shoot the breeze over a cold one.

The importance of community involvement within the SharePoint space is about creating a better understanding about a product we sell to our clients to give them a greater return on investment (ROI) which makes a happy client which makes us happy which makes our bosses happy which betters our careers and so on and so forth. The more we learn and share the better the product will grow and thus ensuring job security. I could go on and on but you get the idea.

I do believe the most important reason for community involvement with the SharePoint space is that it makes me proud to be part of an industry that takes work home with them. Not because we have to, because we want to.

Post to Twitter

SharePoint 2010 Hide left quick launch navigation bar

So you chose a SharePoint 2010 template and everything is working out besides the fact that you cannot get rid of the left quick launch navigation bar. Dont stress, this site has a load of great tips and tricks to get you on your way http://erikswenson.blogspot.com/2010/01/sharepoint-2010-base-css-classes.html

Hiding the left quick launch navigation bar is easy. Add a Content Editor WebPart (CEWP) to your site and add the following code by editing the HTML source of the WebPart

<Style> 
body #s4-leftpanel 
{ 
display: none; 
} 
.s4-ca 
{ 
margin-left: 0px; 
} 
</style> 

Set the WebPart to hidden and once you save the page the left quick launch navigation bar will disappear

Post to Twitter

SharePoint 2010 OOTB Classifieds free site template

I got asked by a client if we could put together a Classifieds (Selling Stuff) site template for SharePoint 2010 together. What’s the catch? No code or workflows! It had to only use OOTB (Out of the box) SharePoint 2010 features.

So I snapped something together and thought I might as well share it 🙂

This is the deployment guide
This is the WSP solution

Post to Twitter