CREATE PROCEDURE dbo.AddManager @Podr_naim varchar ( 100 ), @Login varchar ( 100 ),
@Name varchar ( 50 ), @Famili varchar ( 50 ), @Last_Name varchar ( 50 ), @IsBoss bit, @Pass varchar ( 50 ),
@Position varchar ( 150 ) AS
EXEC sp_addlogin @Login, @Pass, 'MyDataBase'
EXEC sp_grantdbaccess @Login, @Login
DECLARE @SID varbinary ( 85 )
SELECT @SID =  sid FROM master.dbo.syslogins where name = @Login
DECLARE @Podr_id bigint
SELECT @Podr_id = podr_id FROM Otdel where naimen = @Podr_naim
INSERT Managers (SID, Podr_id, Login_name, [Name], Famili, Last_name, IsBoss, [Position])
VALUES (@SID, @Podr_id, @Login, @Name, @Famili, @Last_Name, @IsBoss, @Position)
DECLARE @RoleName varchar( 100 )
SELECT @RoleName = Role_Name FROM OtdelToRole WHERE Otdel_id = @Podr_id
IF NOT EXISTS(SELECT name FROM sysusers where issqlrole =  1  and name = @RoleName)
	BEGIN
	INSERT OtdelToRole (Otdel_id,Role_Name) VALUES (@Podr_id,@Podr_naim)
	EXECUTE sp_addrole @Podr_naim
	END
EXECUTE sp_addrolemember @RoleName, @Login
DECLARE @SQLString nvarchar( 512 )
SET @SQLString = 'GRANT EXECUTE ON ReturnMenu TO ' + @Login  
EXECUTE sp_executesql @SQLString
GO

sp_addrolemember