create user for a login

Question

create user for a login

sqlfriend

SSC Guru

Points: 52794
More actions
June 26, 2015 at 2:09 pm

#309521

I am using a SQL server 2008 database.
I am working on a windows user.
I noticed when I create a user in a database for a login, even that login does not exist in the database, the user is still successful created.
I suppose it should error out.
Or I miss something?
I am using this script:
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'mydomain\myuser')
CREATE USER [mydomain\myuser] FOR LOGIN [mydomain\myuser] WITH DEFAULT_SCHEMA=[dbo]
GO
Thanks,

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

sqlfriends (6/26/2015)
I am using a SQL server 2008 database.
I am working on a windows user.
I noticed when I create a user in a database for a login, even that login does not exist in the database, the user is still successful created.
I suppose it should error out.
Or I miss something?
I am using this script:
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'mydomain\myuser')
CREATE USER [mydomain\myuser] FOR LOGIN [mydomain\myuser] WITH DEFAULT_SCHEMA=[dbo]
GO
Thanks,

Yes, logins exist at the server level, users at the database level. Your test above is looking to see if the user exists, not the login. To find the login you would actually check sys.server_principles.

Edit, actually, that should be at the instance level regarding logins.

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thanks,

Yes, I do need to check if the user exists before I create the user for the login.

Even so, I think the create user statement should error out, because there is no login that exists.

You cannot create a user for a login that does not exist, correct?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

sqlfriends (6/26/2015)
Thanks,
Yes, I do need to check if the user exists before I create the user for the login.
Even so, I think the create user statement should error out, because there is no login that exists.
You cannot create a user for a login that does not exist, correct?

But your code is checking for a user, not a login. The DMV sys.database_principles holds user information for the database where sys.server_principles holds the information for logins for the instance.

https://msdn.microsoft.com/en-us/library/ms187328.aspx

https://msdn.microsoft.com/en-us/library/ms188786.aspx

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

Sorry, that is not what I am asking.

I know login is at instance level, user is in the database level.

Let us remove the code about checking exists of the user.

If I only use this line and run it in SSMS:

CREATE USER [mydomain\myuser] FOR LOGIN [mydomain\myuser] WITH DEFAULT_SCHEMA=[dbo]

The login does not exist, I suppose it should error out, but it did not give any error, and created a user in the database for a login that does not exist.

Thanks,

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

sqlfriends (6/26/2015)
Sorry, that is not what I am asking.
I know login is at instance level, user is in the database level.
Let us remove the code about checking exists of the user.
If I only use this line and run it in SSMS:
CREATE USER [mydomain\myuser] FOR LOGIN [mydomain\myuser] WITH DEFAULT_SCHEMA=[dbo]
The login does not exist, I suppose it should error out, but it did not give any error, and created a user in the database for a login that does not exist.
Thanks,

Can't verify this at home since I don't have SQL Server installed on my new laptop but this is directly from BOL regarding CREATE USER (emphasis mine):

LOGIN login_name

Specifies the SQL Server login for which the database user is being created. login_name must be a valid login in the server. When this SQL Server login enters the database it will acquire the name and ID of the database user that is being created.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Remember that a windows user can be granted access through group membership as well.