1

What is the difference between SQL Server logins and database users, and how can I use the users inside my code for the privileges given to each user since the connection string uses the login only ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ykh
  • 1,775
  • 3
  • 31
  • 57
  • I would recommend you to look for information about how the DBMS actually work in terms of users and their access, permissions, before posting such a broad question – aleafonso Oct 27 '11 at 08:02

2 Answers2

3

A SQL Server login stores the informations to authenticate a user at server level.

After a user is authenticated, you might want to authorize him to use some of the databases the server hosts. You do that by mapping the login to a database user or in other word grant that login access to one or more databases.

What privileges do you want to set by code? For example you can use SMO to administer your server or you can use Sql Statements like GRANT to change permissions.

Jan
  • 15,802
  • 5
  • 35
  • 59
  • What i don't get is how to use the SQL user in my code first to set the privlages and secnod not to allow specfic users to enter or access the database ? – ykh Oct 27 '11 at 07:16
1

I think this is a very useful question. Just to add my two cents: from msdn:

A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL Server login). Note Note

To use SQL Server Authentication, the Database Engine must use mixed mode authentication. For more information, see Choose an Authentication Mode.

As a security principal, permissions can be granted to logins. The scope of a login is the whole Database Engine. To connect to a specific database on the instance of SQL Server, a login must be mapped to a database user. Permissions inside the database are granted and denied to the database user, not the login. Permissions that have the scope of the whole instance of SQL Server (for example, the CREATE ENDPOINT permission) can be granted to a login.

http://msdn.microsoft.com/en-us/library/aa337562.aspx#Background

ilmatte
  • 1,732
  • 16
  • 12