Creating New Windows Logins in NAV Through a SQL Sproc

If you’ve been following my on-going battle against the pigs, you’ll be pleased to know that I now have attained three stars on all of the levels in Angry Birds Seasons, Angry Birds, and Angry Birds Rio, so with no more pigs to kill until the next update, I figured I’d write another NAV blog.

photophoto1photo2

One of the reasons I started blogging was to have a place where I could save little gems so they can be easily found later on. One of the directors at Intergen suggested that I blogged the information and then use Google to find it – so that’s what I did. This week I needed to find something I knew I’d done before, but it took me quite a while to track it down, so I figured it’s time to blog the solution so I need never struggle again – and who knows, it may help some of you out too.

Have you ever had a SQL Backup to restore to a SQL Server that does not allow mixed mode (no database logins, only Windows users) and you need to open the NAV database using NAV but don’t have a login? I created the following SQL code to do this, but you may find it useful to be able quickly add all of the Windows users for your team to a customer’s database. Or how about using the ability to call SQL Stored Procs from NAV (recently blogged about by Waldo) to allow new Windows users to be added to NAV from the RoleTailored client – now that sounds useful!

Don’t forget to tell SID

NAV stores security for Windows users with a SID which is a funny string that starts with “S” and the first thing I need is something that will give me the SID string for a Windows login.

Create the following function in the master database.

USE master
GO
CREATE FUNCTION fn_SIDToString
(
 @BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS 
 BEGIN
    IF LEN(@BinSID)%4<>0 RETURN(NULL)
    
    DECLARE @StringSID VARCHAR(100)
    DECLARE @i AS INT
    DECLARE @j AS INT
    
    SELECT @StringSID='S-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,1,1))))
    
    SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,3,6))))
    
    SET @j=9
    
    SET @i=LEN(@BinSID)
    
    WHILE @j<@i
    BEGIN
        DECLARE @val BINARY(4)
        
        SELECT @val=SUBSTRING(@BinSID,@j,4)
        
        SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(BIGINT,CONVERT(VARBINARY,REVERSE(CONVERT(VARBINARY,@val)))))
        
        SET @j=@j+4
    END
    
    RETURN(@StringSID)
END

Between a Sproc and a Hard Place

Once you have this function, the following stored procedure will create a Windows Login, add it as a windows user, and finally make the user a member of the SUPER role. I’ve hard-coded the database name in this sproc, but you should be able to easily make this a parameter to the sproc if you wish.

CREATE PROCEDURE [dbo].[proc_CreateNavUser]
@User  sysname
AS 
SET NOCOUNT ON

DECLARE @SQL VARCHAR(MAX)
DECLARE @RetVal INTEGER

SET @RetVal=0

BEGIN TRY
    IF(SELECT COUNT(*)
       FROM master.sys.server_principals
       WHERE name=@User)=0
    BEGIN
        SET @SQL='use master CREATE LOGIN ['+@User+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master]'
        
        EXECUTE(@SQL);
    END
    
    IF(SELECT COUNT(*)
       FROM [Demo Database NAV (6-0)].sys.database_principals
       WHERE name=@User)=0
    BEGIN
        SET @SQL='use [Demo Database NAV (6-0)] CREATE USER ['+@User+'] FOR LOGIN ['+@User+']'
        
        EXECUTE(@SQL);
    END
END TRY
        BEGIN CATCH
    SELECT Retval=@RetVal
    
    RETURN
END CATCH

BEGIN TRY
    SET @RetVal=1
    
    INSERT INTO [Demo Database NAV (6-0)].dbo.[Windows Login]([SID])
    SELECT master.dbo.fn_SIDToString(sp.sid)
    FROM master.sys.server_principals sp
    WHERE sp.name=@User AND master.dbo.fn_SIDToString(sp.sid) NOT IN(SELECT sid
                                                                     FROM [Demo Database NAV (6-0)].dbo.[Windows Login])
          
--Add Roles (SUPER)
          
    INSERT INTO [Demo Database NAV (6-0)].dbo.[Windows Access Control]([Login SID],[Role ID],[Company Name])
    SELECT master.dbo.fn_SIDToString(sp.sid),'SUPER',''
    FROM master.sys.server_principals sp
    WHERE sp.name=@User AND master.dbo.fn_SIDToString(sp.sid) NOT IN(SELECT [Login SID]
                                                                     FROM [Demo Database NAV (6-0)].dbo.[Windows Access Control]
                                                                     WHERE [Role ID]='BASIC')
    
    SELECT Retval=@RetVal
END TRY
        BEGIN CATCH
    
END CATCH

Enjoy the srpoc. If you do write something that uses this – like a tool to create Windows logins in the RoleTailored client, why not share your solution too?

Now it’s time to play Cut the Rope.


Related
Recommended