Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
OpenUtilities
  • Product Communities
OpenUtilities
OpenUtilities Wiki SQL Server Login
    • Sign In
    • +OpenUtilities
    • Access Service Request Manager
    • +Bentley sisHYD Multi Utility
    • Design Features - promis.e Functionality - Promis.e Standard
    • +OpenUtilities sisNET - English
    • +OpenUtilities Substation
    • Promis.e Paths
    • +Promis.e Wiki
    • Replace Family - Find_Replace - Promis.e
    • SQL Server Login

    You are currently reviewing an older revision of this page.

    • History View current version

    Creating an SQL Server Login [TN]

    Document Information

    Document Type: TechNote

    Product(s): promis•e, Bentley Substation

    Version(s): All

    Original Author: Matt_P

    Overview

    promis•e and Bentley Substation can be configured to use either Windows Authentication or SQL Server Authentication to connect to the SQL Server hosting the project database.  To use SQL Server Authentication, a valid SQL Server Login must exist on the server and its name and password must be known and entered into the Setup dialog of promis•e or Bentley Substation.

    After creating the project database with the Project Database Utility and using Windows Authentication, the SQL Server may not have a SQL Server Login that users can specify on the Setup dialog, depending on the application and version.  Similarly, the SqlServerProject.sql script for some versions does not include lines for creating an SQL login.

    After restoring a backup of a project database, the SQL Server may already have a SQL Server Login but it will not be properly associated with the restored database, preventing the login from being used to connect to the database.

    To address the above issues, an SQL Server Login can be created and/or associated with database.

    Script

    The following script adds the "ecaduser" login with a "Ecad1pass" password for SQL Server Authentication access to the SQL Server and adds the "ecaduser" user to the promiseX database and links it to the login.  It can be executed using SQL Server Management Studio.  Substitute the correct database name for “promiseX” and make other changes appropriate to the situation before executing the script.


    if not exists (select * from syslogins WHERE loginname=N'ecaduser')
    begin
       EXEC sp_addlogin 'ecaduser','Ecad1pass','promiseX';
       ALTER LOGIN [ecaduser] WITH DEFAULT_DATABASE=[promiseX], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
    end
    GO
    use promiseX
    GO
    if not exists(select * from sysusers WHERE name=N'ecaduser')
       EXEC sp_adduser 'ecaduser','ecaduser','db_owner'
    GO
    ALTER USER ecaduser WITH LOGIN = ecaduser;
    GO
    ALTER USER ecaduser WITH DEFAULT_SCHEMA = dbo;
    GO


    See Also

    Creating and Updating the Project Database

    SELECTsupport TechNotes and FAQs

    External Links

    How to: Create a SQL Server Login

    Bentley Technical Support KnowledgeBase

    Bentley LEARN Server

    Comments or Corrections?

    Bentley's Technical Support Group requests that you please submit any comments you have on this Wiki article to the "Comments" area below. THANK YOU!

     

    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies