locked
Checking SQL Server User Roles and Creating SQL Server Users using VB.NET RRS feed

  • Question

  • Hello gurus!

    Firstly I want to apologies if this question is out of place here.. if someone can direct me to the correct forumn great and Thanks!

    I have a VB.NET application which uses its own Backend Database (MSSQL Server). I need to distribute this application to sites where there will be an existing SQL Server.

    So I will need to Create the Database on this server. The Application includes methods for building the database on startup if not already connected to one.

    However the users windows logon may not have the correct permission to connect and create a Database on the Server. I have a DB Setup form in my application which asks for the Servname, Username, Password and Database name. I have catered for Windows Authentication and SQL Server Authentication within the form - the user makes the choice.

    Assuming they enter a Username and Password for SQL Server Athentication then I will be trying to connect using this user and create the database on the given server. The following is my outline logic:-

                                                                      Create db Process
                                                                                 |
                                                                                 |
                                                                   Check Credentials
                                                                       /                 \
                                                     Dont Work /                    \ Work
                                                                    /                       \
                                                   Ask if Credentials                 \ 
                                                  should be created?                 \
                                                            |                                  |
                                                 Prompt for sa                     Create DB
                                                            |                                 Using
                                                            |                             Credentials
                                              Create DB Instance
                                                       using sa
                                                           |
                                                           |
                                                Create Credentials
                                             with New DB as default
                                                      using sa
                                                           |
                                                           |
                                              Generate DB Tables
                                               Using Credentials

    So all clear on the western front!

    I am using :-
    Imports System.Data.Sql
    Imports System.Data.SqlClient

    What I need to know is how do I access Users to check roles on SQL Server? (Code Samples?)
    How do I create the New user on the server and assign ownership of the new database to the new user?
    Is this in fact the best method for achieving this?

    Thanks in advance for any help forthcoming!
    CHeers
    Jeff
    Perth in Western Australia!
    • Moved by OmegaMan Tuesday, May 26, 2009 8:50 PM (From:Regular Expressions)
    Friday, May 22, 2009 3:55 AM

All replies

  • I don't think this is a Regular Expression question, you should post this in the SQL Server forum.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, May 25, 2009 2:56 PM
  • Below are t-sql for your questions, you can find corresponding SMO syntax:

    To check if a login has the permission of create database, use this query and if the result show "create any database" permission, then the login can create database:

    SELECT * FROM SYS.SERVER_PERMISSIONS WHERE GRANTEE_PRINCIPAL_ID=(SELECT  PRINCIPAL_ID FROM SYS.SERVER_PRINCIPALS WHERE NAME ='LOGIN_NAME')


    To alter database ownership:
    ALTER AUTHORIZATION ON DATABASE::USER_DB_NAME TO LOGIN_NAME


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, May 30, 2009 12:58 AM