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.
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!
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
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.