5

I have the following script and I keep getting errors when it is executed.

USE [master]
GO

If Not EXISTS (Select loginname from [master].[dbo].[syslogins]
    Where name = 'xxxx' and dbname = 'xxxx-xxxx')
BEGIN
    CREATE LOGIN [xxxx] WITH PASSWORD=N'xxxxx', DEFAULT_DATABASE=[xxxx-xxxx], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    ALTER LOGIN [xxxx] ENABLE
END
GO

USE [xxxx-xxxx]
ALTER USER [xxxx] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD MEMBER [xxxx]
ALTER ROLE [db_datawriter] ADD MEMBER [xxxx]
ALTER ROLE [db_owner] ADD MEMBER [xxxx]
GO

I have tried several permutations of this script with no success. It does create the login but won't let me alter the "user" or "role" properties. When I can get it to work with no errors, it does not make the changes to the user profile.

3
  • 3
    You need to create the user (CREATE USER [xxxx]) before you try to alter it.
    – Dan Guzman
    Oct 3, 2019 at 0:31
  • Can you do the alter user with the user that executes the script?
    – Marco
    Oct 3, 2019 at 6:47
  • The CREATE USER must be something that the GUI does when creating the login. I used the "Create Script" option in the GUI and it did not show this as a needed command. I moved the CREATE USER into the BEGIN/END block because I want the script to be able to run without errors if it is ran more than once.
    – Eldon Z
    Oct 3, 2019 at 11:55

2 Answers 2

2

You are missing create user before you alter it.

Login - gets you entry to the server Database User - gets you entry to a particular database

so in your script, add create user from login ...

USE [xxxx-xxxx]
create user [xxxx] from login [xxxx] <--- this is needed !
ALTER USER [xxxx] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD MEMBER [xxxx]
ALTER ROLE [db_datawriter] ADD MEMBER [xxxx]
ALTER ROLE [db_owner] ADD MEMBER [xxxx]
GO

few things :

1

Few Basic ideas.

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).

A user is a database level security principal. Logins must be mapped to a database user to connect to a database. A login can be mapped to different databases as different users but can only be mapped as one user in each database. In a partially contained database, a user can be created that does not have a login. For more information about contained database users, see CREATE USER (Transact-SQL). If the guest user in a database is enabled, a login that is not mapped to a database user can enter the database as the guest user.

Logins are distinct from database users. You must map logins or Windows groups to database users or roles in a separate operation. You then grant permissions to users or roles to access database objects.

As mentioned by @Dan Guzman you need to create a user in the database [xxxx-xxxx] mapped to the login xxxx you are creating. Then you can run the 2nd section of your code. Example:

CREATE LOGIN WanidaBenshoof   
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';  
USE AdventureWorks2012;  
CREATE USER Wanida FOR LOGIN WanidaBenshoof 

In case you are thinking of contained user. Contained Database Users - Making Your Database Portable

Reference:

  1. Create a Login
  2. Create a Database User

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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