set PLM Suite application user

-- ===================================================
-- Procedure Name: fb_plm_access_control
-- Function      : set PLM Suite application user
-- Failure return: 1
-- Success return: 0
--
-- Parameters    :
-- @login_name   : application user name, create a account if the user does not exists, otherwise remain it.
-- @password     : for the exists user, if the password is null, then remain its old password,
--               : for the new user, it can not be null.
-- @db_name      : PLM Suite database name
-- ===================================================
IF EXISTS (select * from sysobjects where id = object_id(N'fb_plm_access_control') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE fb_plm_access_control
GO

CREATE PROCEDURE fb_plm_access_control
        (
        @login_name NVARCHAR(100),
        @password NVARCHAR(100) = null,
        @db_name NVARCHAR(100)
        )
--WITH ENCRYPTION
AS
DECLARE @result   INT
DECLARE @user_exists  INT
DECLARE @execute_sp_role NVARCHAR(100)
DECLARE @create_table_role NVARCHAR(100)
DECLARE @object_name  NVARCHAR(120)
DECLARE @object_type  NVARCHAR(10)
DECLARE @sql   VARCHAR(200)
DECLARE @error_msg  VARCHAR(100)
BEGIN
-- check input paramters.
IF (@login_name IS NULL) OR (LTRIM(@login_name) = '') OR (lower(@login_name) = 'sa')
  BEGIN
   RAISERROR ('The login_name can not be null or sa.' , 16, 1) WITH NOWAIT
   RETURN 1
  END
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
  BEGIN
   SET @error_msg = 'The database ' + @db_name + ' does not exits.'
   RAISERROR (@error_msg, 16, 1) WITH NOWAIT
   RETURN 1
  END

-- if user does not exits, create it
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = @login_name)
  BEGIN
   SET @user_exists = 0
   IF (@password IS NULL) OR (@password = '')
    BEGIN
     RAISERROR ('For new application user, the password can not be null.' , 16, 1) WITH NOWAIT
     RETURN 1
    END
   EXEC sp_addlogin @loginame = @login_name, @passwd = @password, @defdb = @db_name, @deflanguage = @@language
   IF @@ERROR <> 0
    RETURN 1
  END
ELSE
  BEGIN
   SET @user_exists = 1
   IF (@password IS NOT NULL) AND (LTRIM(@password)<>'')
    BEGIN
     EXEC sp_password @old = null, @new = @password, @loginame = @login_name
     IF @@ERROR <> 0
      RETURN 1
    END
  END
  
-- create the execute sp role if it does not exists
SET @execute_sp_role = N'db_procexecutor'
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @execute_sp_role AND issqlrole = 1)
  BEGIN
   EXEC sp_addrole  @rolename = @execute_sp_role 
   IF @@ERROR <> 0
    RETURN 1
  END
  
-- create the create table role if it does not exists
SET @create_table_role = N'db_createtable'
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @create_table_role AND issqlrole = 1)
  BEGIN
   EXEC sp_addrole  @rolename = @create_table_role 
   IF @@ERROR <> 0
    RETURN 1
  END

-- grant privilege to the role
DECLARE cur_sp_fun CURSOR 
  FAST_FORWARD 
  FOR 
  SELECT name, xtype
  FROM   sysobjects 
  WHERE  xtype in (N'P', N'FN', N'IF', N'TF')
  AND   uid = (select uid from sysusers where name = USER_NAME()) 
OPEN cur_sp_fun
FETCH NEXT FROM cur_sp_fun INTO @object_name, @object_type
WHILE @@FETCH_STATUS = 0
  BEGIN
   IF @object_type = N'P'
    BEGIN
    SET @sql='GRANT EXECUTE ON '+@object_name+' TO '+@execute_sp_role
    END
   IF @object_type = N'FN'
    BEGIN
    SET @sql='GRANT REFENENCES ON '+@object_name+' TO '+@execute_sp_role
    SET @sql=' GRANT EXECUTE ON '+@object_name+' TO '+@execute_sp_role
    END
   IF @object_type IN (N'IF',N'TF')
    BEGIN
    SET @sql='GRANT SELECT ON '+@object_name+' TO '+@execute_sp_role
    END
   EXEC (@sql)
   IF @@ERROR <> 0
    BEGIN
     SET @result = 1
     BREAK
    END
   FETCH NEXT FROM cur_sp_fun INTO @object_name, @object_type
  END
CLOSE cur_sp_fun
DEALLOCATE cur_sp_fun
IF @result = 1
  RETURN 1
  
SET @sql = 'GRANT CREATE TABLE TO ' + @create_table_role
EXEC (@sql)
IF @@ERROR <> 0
  RETURN 1

-- grant access database privilege to application user
IF @user_exists = 1
  EXEC sp_revokedbaccess @name_in_db = @login_name
IF @@ERROR <> 0
  RETURN 1

EXEC sp_grantdbaccess @loginame = @login_name, @name_in_db = @login_name
IF @@ERROR <> 0
  RETURN 1
-- grant role to application user
EXEC sp_addrolemember @rolename = N'db_datareader', @membername = @login_name
IF @@ERROR <> 0
  RETURN 1

EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @login_name
IF @@ERROR <> 0
  RETURN 1
  
EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @login_name
IF @@ERROR <> 0
  RETURN 1

EXEC sp_addrolemember @rolename = @create_table_role, @membername = @login_name
IF @@ERROR <> 0
  RETURN 1

EXEC sp_addrolemember @rolename = @execute_sp_role, @membername = @login_name
IF @@ERROR <> 0
  RETURN 1

RETURN 0
END
GO