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!
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.
Posted
07-23-2011 9:02 p.m.
by
David Roys