locked
How to set user id and password to a particular database in sql server? RRS feed

  • Question

  • I am using SQL Server 2008. I had connect it as sql server authentication with 'sa' user and my password. In my SQL Server i have 7 databases. Now i want to set a user id and password for a particular database. Where as if anyone want to open that database then he/she needs to enter proper user id and password to see the database. How can i do that. How can i set user id and password for a particular database in sql server 2008. please help me.

     

    Thanks in advance. 

    Wednesday, January 18, 2012 11:52 AM

Answers

  • Hi,

    the option usually here to use user mapping, map any user to allowed database

    to do this:

    Open SSMS and connect to your server go to security folder -> login right click select new login -> user mapping select database

    if you prefer code see this

    USE [master]
    GO
    CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [AdventureWorks]
    GO
    CREATE USER [test] FOR LOGIN [test]
    GO
    


    I hope this is helpful.

     

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com
    • Proposed as answer by Peja Tao Friday, January 20, 2012 1:17 AM
    • Marked as answer by Peja Tao Wednesday, January 25, 2012 1:28 AM
    Wednesday, January 18, 2012 11:59 AM
  • To say that differently, you don't need to set a user-id and password for a database in SQL Server. No user has access to a database, unless they are specifically granted access. And if they are granted access, since they are already authenticated, they don't need to provide an additional password - at that point SQL Server knows who you are.

    Meanwhile, create a login for yourself, make yourself a member of the sysadmin role, and then stop using the sa account. It is a well known account, and it best to disable it. And lastly, if you can use Windows authentication instead of SQL Server authentication. More about that here Choosing an Authentication Mode http://msdn.microsoft.com/en-us/library/ms144284.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Friday, January 20, 2012 1:17 AM
    • Marked as answer by Peja Tao Wednesday, January 25, 2012 1:28 AM
    Wednesday, January 18, 2012 5:09 PM

All replies

  • Hi,

    the option usually here to use user mapping, map any user to allowed database

    to do this:

    Open SSMS and connect to your server go to security folder -> login right click select new login -> user mapping select database

    if you prefer code see this

    USE [master]
    GO
    CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [AdventureWorks]
    GO
    CREATE USER [test] FOR LOGIN [test]
    GO
    


    I hope this is helpful.

     

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com
    • Proposed as answer by Peja Tao Friday, January 20, 2012 1:17 AM
    • Marked as answer by Peja Tao Wednesday, January 25, 2012 1:28 AM
    Wednesday, January 18, 2012 11:59 AM
  • To say that differently, you don't need to set a user-id and password for a database in SQL Server. No user has access to a database, unless they are specifically granted access. And if they are granted access, since they are already authenticated, they don't need to provide an additional password - at that point SQL Server knows who you are.

    Meanwhile, create a login for yourself, make yourself a member of the sysadmin role, and then stop using the sa account. It is a well known account, and it best to disable it. And lastly, if you can use Windows authentication instead of SQL Server authentication. More about that here Choosing an Authentication Mode http://msdn.microsoft.com/en-us/library/ms144284.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Friday, January 20, 2012 1:17 AM
    • Marked as answer by Peja Tao Wednesday, January 25, 2012 1:28 AM
    Wednesday, January 18, 2012 5:09 PM