none
Set username and password after create

    Question

  • Hello

    i create a database in VS201 by SQL server Express now i want seta user name and password fro this database

    1- how can i do it in VS2010

    2- how can i do it in Sql server R2 2008 (if i connect to .\sqlserverExpress)

    thanks


    Hossein Aftabi http://www.internationalsell.com
    Sunday, July 31, 2011 2:05 PM

Answers

  • It's not wholly clear what you are asking for. Are you asking for setting a password on a database like you can in Access? (And which is not very secure, as it can be cracked.) That is not possible in SQL Server.

    In SQL Server 2008 authentication is always on server level. This means that the user is authenticated to the server. The user can then access a database if he has been granted access to that database.

    There are two ways a user can be authenticated to SQL Server. One is Windows authentication. This means that you log on to SQL Server with your Windows login, without specifying your password. Not that you cannot log on to SQL Server with a different Windows login.

    The other way is SQL Server authentication. This means that you provide a username and password stored inside SQL Server. Windows authentication is usually preferred, both for management and security reasons, but there are situations where Windows authentication is not possible.

    Once you have been let into SQL Server, you never specify a password to access a certain database. However, you may have to specify a password to access encrypted data.

    Now that you know a little more about users and logins in SQL Server, maybe you can think a little more of what you want to achieve.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Tuesday, August 02, 2011 5:58 AM
    • Marked as answer by Peja Tao Friday, August 05, 2011 2:33 PM
    Sunday, July 31, 2011 7:13 PM
  • You are using Windows Authentication. You used a Windows user name and password (or possibly no passord) when you logged into Windows. So Windows knows who you are, meaning Windows has authenticated you. SQL Server is trusing Windows. So you do not need to provide user name and password. And since you installed SQL Server, SQL Server made you an administrator.

    To test this, Log into Windows as different Windows user. If you try to connect to SQL Server, you will probably fail. Because you haven't told SQL Server to trust that second Windows user.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Tuesday, August 02, 2011 6:00 AM
    • Marked as answer by Peja Tao Friday, August 05, 2011 2:33 PM
    Monday, August 01, 2011 9:02 PM
  • And there is no way to tell this Crystalviewer that you are using Windows authentication? (May also be called "trusted connection" or "integrated security".)

    But if you want to use SQL authentication, you must first enable this on your instance, if you did do so at Setup. For this you need SQL Server Management Studio Express. Right-click the server in the Object Explorer, select Properties and then the Security tab. You need to restart SQL Server.

    Once you have done this you can run this in your database:

    CREATE LOGIN user1 WITH PASSWORD = 'TopP$ecrEEtT'
    CREAET USER user1

    And then grant this user the same permissions that you granted the user you are using now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Tuesday, August 02, 2011 6:00 AM
    • Marked as answer by Peja Tao Friday, August 05, 2011 2:33 PM
    Monday, August 01, 2011 10:04 PM

All replies

  • It's not wholly clear what you are asking for. Are you asking for setting a password on a database like you can in Access? (And which is not very secure, as it can be cracked.) That is not possible in SQL Server.

    In SQL Server 2008 authentication is always on server level. This means that the user is authenticated to the server. The user can then access a database if he has been granted access to that database.

    There are two ways a user can be authenticated to SQL Server. One is Windows authentication. This means that you log on to SQL Server with your Windows login, without specifying your password. Not that you cannot log on to SQL Server with a different Windows login.

    The other way is SQL Server authentication. This means that you provide a username and password stored inside SQL Server. Windows authentication is usually preferred, both for management and security reasons, but there are situations where Windows authentication is not possible.

    Once you have been let into SQL Server, you never specify a password to access a certain database. However, you may have to specify a password to access encrypted data.

    Now that you know a little more about users and logins in SQL Server, maybe you can think a little more of what you want to achieve.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Tuesday, August 02, 2011 5:58 AM
    • Marked as answer by Peja Tao Friday, August 05, 2011 2:33 PM
    Sunday, July 31, 2011 7:13 PM
  • Hello and thanks for answer

    i description about my problem: in first time i create a database with VS2010 With add new item .

    when i create a sql express database it create with out user name and password

    after promote my project for use crystalviewer it asked me about username and password for connect to database

    however i don't set any username and password and i set Windows authentication for login to Database

    i aked about this problem an sap forum and in MSDN Forums nout i don't get god answer

    ok

    in final i change my way and i think it's better that i set a username and password in my database

    ok

    Now i have a database without username and password (Windows authentication)

    and i need set user name and password for mydatabase that i create  it many time ago

    Thanks

     


    Hossein Aftabi http://www.internationalsell.com
    Monday, August 01, 2011 6:42 PM
  • You are using Windows Authentication. You used a Windows user name and password (or possibly no passord) when you logged into Windows. So Windows knows who you are, meaning Windows has authenticated you. SQL Server is trusing Windows. So you do not need to provide user name and password. And since you installed SQL Server, SQL Server made you an administrator.

    To test this, Log into Windows as different Windows user. If you try to connect to SQL Server, you will probably fail. Because you haven't told SQL Server to trust that second Windows user.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Tuesday, August 02, 2011 6:00 AM
    • Marked as answer by Peja Tao Friday, August 05, 2011 2:33 PM
    Monday, August 01, 2011 9:02 PM
  • And there is no way to tell this Crystalviewer that you are using Windows authentication? (May also be called "trusted connection" or "integrated security".)

    But if you want to use SQL authentication, you must first enable this on your instance, if you did do so at Setup. For this you need SQL Server Management Studio Express. Right-click the server in the Object Explorer, select Properties and then the Security tab. You need to restart SQL Server.

    Once you have done this you can run this in your database:

    CREATE LOGIN user1 WITH PASSWORD = 'TopP$ecrEEtT'
    CREAET USER user1

    And then grant this user the same permissions that you granted the user you are using now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Tuesday, August 02, 2011 6:00 AM
    • Marked as answer by Peja Tao Friday, August 05, 2011 2:33 PM
    Monday, August 01, 2011 10:04 PM