locked
Within the Database User - New dialogue window, what User type should I choose from the drop-down RRS feed

  • Question

  • Sooooo, I've added my User to the SQL Server Security. Now I have to add the Security User to each Database. When I drill into the Database and Security and <Click> on New user...and within the Database User - New dialogue window, what should I choose from the User type: drop-down? SQL user without login or Windows user?

    Kind of new at setting up our Report Manager Security and SSRS.

    Thanks for your review and am hopeful for a reply.

    Monday, December 28, 2015 4:08 PM

Answers

  • Hi
    You should create a database user by selecting "SQL user with login".

    Code:

    USE [Sample]
    GO
    CREATE USER [SampleDB_User] FOR LOGIN [ADGroup\SampleDB_Users]
    

    Also, a database user is automatically created with the same username when you map (or assign) any databases roles to the login that you have earlier created.

    Code:

    USE [Sample]
    GO
    CREATE USER [SampleDB_User] FOR LOGIN [ADGroup\SampleDB_Users]
    GO
    USE [Sample]
    GO
    ALTER ROLE [db_datawriter] ADD MEMBER [SampleDB_User]
    GO
    USE [Sample]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [SampleDB_User]
    GO

    Thanks,

    Kiran

    Monday, December 28, 2015 7:27 PM
  • Sai is very correct. Not the least because he uses SQL commands and not the UI. All the UI does is to create SQL commands, and it is not always that helpful.

    "SQL User" in this context refers to a to a user/login that is defined in SQL Server. That is, an SQL User with a login, logs on to SQL Server specifying username and password, and there is no relation to Windows. This is good when Windows authentication does not work, but else you don't want it. An SQL user without login is a login which is local to the database. This user cannot login, but it can be impersonated. This is useful for testing and other advanced scenarios, but it is absolutely not what you wanted in this situation.

    Monday, December 28, 2015 10:08 PM

All replies

  • First a quick discussion on some confusing terminology. Windows has Windows "users" and "groups". SQL Server has "logins" for authorizing access to the SQL Server as a whole, and then database "users" to specify (map) which logins can connect to various databases. The SQL Server login can represent a Windows user (or group), or it can be authenticated by SQL Server (a SQL Server authentication login) and be unrelated to a Windows user (or group).

    I think (when you said you "added my User to the SQL Server Security") that you meant that you have created a "login." So when you create the database "user", you should create a "SQL user with login" and then specify the name of the login (which I think you created).

    There is a type of user called "contained database user" which allows you to bypass the step of creating a login. To use this (which is encouraged) the database must be specially configured as a contained database. That's not the default configuration for databases, and since I think you have already created a login, that's probably not what you are doing. But if you are, then you can select Windows user.

    Sorry if I have confused you more.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, December 28, 2015 4:57 PM
  • No...No....I appreciate your help Rick. Let me further clarify by adding "SQL Server Security", I did indeed mean that I added an Active Directory Group. From what I have seen, I think best business practice is to control SQL Server Security by Windows Active Directory Groups. That way you don't have to get involved in the comings and goings of individuals. They are simply added to Active Directory and in the correct groups. So that being said, what should I pick within the Database User - Newdialogue window and the User type drop-down? I am showing these possible values within the drop-down:

    • SQL user with login
    • SQL user without login
    • User mapped to a certificate
    • User mapped to an asymmetric key
    • Windows user

    My thinking was to choose SQL user without login since I've already successfully added the Windows Active Directory Group as a SQL Server User. But then I started thinking maybe I should choose Windows user since that's what the Active Directory Group essentially is.

    Your thoughts........

    Monday, December 28, 2015 7:00 PM
  • Hi
    You should create a database user by selecting "SQL user with login".

    Code:

    USE [Sample]
    GO
    CREATE USER [SampleDB_User] FOR LOGIN [ADGroup\SampleDB_Users]
    

    Also, a database user is automatically created with the same username when you map (or assign) any databases roles to the login that you have earlier created.

    Code:

    USE [Sample]
    GO
    CREATE USER [SampleDB_User] FOR LOGIN [ADGroup\SampleDB_Users]
    GO
    USE [Sample]
    GO
    ALTER ROLE [db_datawriter] ADD MEMBER [SampleDB_User]
    GO
    USE [Sample]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [SampleDB_User]
    GO

    Thanks,

    Kiran

    Monday, December 28, 2015 7:27 PM
  • Sai is very correct. Not the least because he uses SQL commands and not the UI. All the UI does is to create SQL commands, and it is not always that helpful.

    "SQL User" in this context refers to a to a user/login that is defined in SQL Server. That is, an SQL User with a login, logs on to SQL Server specifying username and password, and there is no relation to Windows. This is good when Windows authentication does not work, but else you don't want it. An SQL user without login is a login which is local to the database. This user cannot login, but it can be impersonated. This is useful for testing and other advanced scenarios, but it is absolutely not what you wanted in this situation.

    Monday, December 28, 2015 10:08 PM
  • You might find it helpful to review my article on the SQL Server TechNet wiki: Database Engine Permission Basics http://social.technet.microsoft.com/wiki/contents/articles/4433.database-engine-permission-basics.aspx

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, December 29, 2015 5:45 PM