1

In SQL Server, when setting permissions with a Grant statement, we do it on Users, not Logins. For example:

CREATE LOGIN login1 WITH PASSWORD = 'sssssssss';
use mydb;
CREATE USER user1 FOR LOGIN login1
grant delete on table1 to user1

So, users may or may not be able to do a certain task on a certain table. But, when connecting to a database from JDBC and etc, we give the login name and the password (login1 and 'sssssssss' in here) in the connection string, not the User name!

So where do these grant statements show themselves?! We never mentioned User1 in the code! And, if we define two users for same login, each with different permissions, and then connect to database from JDBC, which of these user permissions are considered?

2

I am not quite sure that I understand your question clearly, so please bear with me on the issue of Logins and Users.

It appears to be, in your case, SQL Server logins and not Active Directory accounts, but they behave essentially the same within a server and database.

Also, for what it is worth, it seems that some step or steps are missing from your question. No big deal overall.

Login: (Server Level)

You might create login Login1, which has a SID of 0x14151617181920212223242526099097 and grant it some rights. These rights belong to the login.

User: (Database Level)

When you create User1 for Login1 the user inherits the same SID of 0x14151617181920212223242526099097.

So, inside your database the server login Login1 is actually executed by the database user User1 so the User is accredited with the permission.

Because, as you see, the two are really the same account. The name of the User is absolutely meaningless in terms of execution. It is the SID that matters.

  • @John - Perhaps you could show the code you would use to set up the two users and show what the results of your work actually produces. Please clarify the question. – RLF Apr 17 '16 at 21:48
4

A SQL Server login is used to authenticate the principal at the server level, and to provide server level permissions.

A database user maps to zero or one login and is used to provide a security context within the database. Database permissions are assigned to database principals, including roles, not directly to logins.

User names are generally not specified in connection strings. The exception is when a user is authenticated at the database level, and therefore has no login.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.