Create Login and User SQL Query

Transact-SQL
,
1

Hey all, I've got a question.

While I'm new to SQL Server, I'm an old hat at SQL in general with specific experience in Access and MySQL. Because of that, I figured it would be relatively easy for me to just jump in to a live environment and get started. Most of it has been editing/updating basic SQL queries...

Unfortunately, I'm NOT familiar with the way Logins and Users are created in SQL Server and am running into some trouble with my current task.

I need to create a SQL query that creates a Login and User and adds it to the 'public' and 'db_datareader' groups for a specific database.

From what I understand from the MSDN pages, the SQL query should be something like below:

CREATE LOGIN MyUser
	WITH PASSWORD = 'MyPassword'
	CHECK_EXPIRATION = NO
	CHECK_POLICY = NO;
USE MyDatabase;
CREATE USER MyUser
	FOR LOGIN MyUser
	WITH PASSWORD = 'MyPassword';
EXEC sp_addrolemember db_datareader MyDatabase;
GO

Is this correct?

The server this is going to be run on is running SQL Server 2008 which is why I used sp_addrolemember instead of ALTER ROLE in the query. If ALTER ROLE works in SQL Server 2008, I'd prefer to use it for forward compatibility.

2

It's the right idea. Syntax note:

EXEC sp_addrolemember 'db_datareader', 'MyDatabase';

Also note that you can grant login permissions based on Windows accounts. eg::

create login [domain\user] from windows

If you're managing a db with lots of users, it's often better to manage at the Windows group level. Same syntax as above but you only have to add the group once, then add users to the group

1 Like
3

Thanks for the reply! I'll go ahead and update my SQL Queries per your note.

I was using a database Login instead of a Windows Login because this is going to be an account use by a script that will be exporting data to a vendor (exports data to CSV, then transmits it using SSH to their server). In this situation would it still be better to use a Windows based Login?

4

Depends on a few things.

  1. If you will invoke the script manually, you can just use your own windows login
  2. If you will set up an Agent job, you can also set up a proxy account to run it, which can be a windows account
  3. if you're going to write a SSIS export package, you can likewise schedule and run it via Agent and a proxy account.

You might want to play with a few options and see which one fits your business processes best.

2 Likes
5

The current plan was to execute the script using Task Scheduler. I'll look into Agents and SSIS packages though since those look like they might be better options.

Thanks again!

6

Some minor corrections:
CREATE LOGIN: Added commas between the WITH entries;
CREATE USER: Removed the PASSWORD = entry (specified at LOGIN level) and changed "FOR" to "FROM" just for readability;
sp_addrolemember: Added quotes and changed to user name rather than db name.
GO: get rid of it.

CREATE LOGIN MyUser
	WITH PASSWORD = 'MyPassword',
	CHECK_EXPIRATION = NO,
	CHECK_POLICY = NO;
USE MyDatabase;
CREATE USER MyUser
	FROM LOGIN MyUser;
EXEC sp_addrolemember 'db_datareader', 'MyUser';

experience in Access and MySQL. Because of that, I figured it would be relatively easy for me to just jump in to a live environment

Not necessarily! :smiley: ANSI standards notwithstanding, all dbms vendors have managed to in some ways isolate their own versions of SQL.

7

Sorry for not replying sooner, but I just ran a test using ScottPlecher's corrections and am getting the following error message:

"Incorrect syntax near CHECK_EXPIRATION"

After a little but of looking around on the interwebs, I ran across a couple posts saying that I should be using double-apostrophes to surround my password instead of just single apostrophes (and instead of using quotes/double-quotes).

WITH PASSWORD = ''MyPassword'',

Is this what's wrong? Or is there something else going on that I'm not seeing?

Also, I'm not sure if this is relevant or not, but the queries are being run from sqlcmd.exe. I'm writing the queries to a SQL file and then running that instead of running the queries directly.

P.S.
Not sure how they disappeared from my SQL command but the commas were in there, I SWEAR! :innocent:

8

So true ... every time I have to dabble with a different DBMS I'm astonished at how much "bespoke" stuff it has ... not to mention all the MSSQL stuff that I naturally use which then, of course, doesn't work on MySQL / PostgreSQL / whatever ...

... luckily 99% of my working life is using just the one flavour of SQL.

9

Probably that.

If you run the command

CREATE LOGIN MyUser
	WITH PASSWORD = 'MyPassword',
	CHECK_EXPIRATION = NO,
	CHECK_POLICY = NO;

that should be fine. If however you have some dynamic SQL that you are "building" and want to execute then you would need to double up the single-quotes:

EXEC 'CREATE LOGIN MyUser
	WITH PASSWORD = ''MyPassword'',
	CHECK_EXPIRATION = NO,
	CHECK_POLICY = NO;'

(Bah! the Poxy Forum Software is not formatting the string to make it easy to see ...)

and you might be doing that because you have the MyUser and MyPassword in variables

DECLARE @SQL nvarchar(4000)
@SQL = 'CREATE LOGIN ' + @MyUser + '
	WITH PASSWORD = ''' + @MyPassword + ''',
	CHECK_EXPIRATION = NO,
	CHECK_POLICY = NO;'
EXEC @SQL

but beware that this type of string concatenation to make a command carries health risks! - particularly SQL Injection (e.g. if someone put their password as

    xxx'; TRUNCATE ReallyImportantTable; --
10

I'm not using any variables or building anything dynamically.

A copy of the SQL queries stored in the file (create_user.sql) is::

CREATE LOGIN test_user
	WITH PASSWORD = 'test_user_password',
	CHECK_EXPIRATION = NO,
	CHECK_POLICY = NO;
USE temp;
CREATE USER test_user
	FROM LOGIN test_user;
EXEC sp_addrolemember 'db_datareader', 'test_user';

The command I'm running when I get the error is:

sqlcmd.exe -S (local) -i "sqltest\create_user.sql" -o "sqltest\create_user_result.txt"

And the exact error message I'm getting (the contents of sqltest\create_user_result.txt) is:

Msg 102, Level 15, State 1, Server SQLSVR1, Line 3
Incorrect syntax near 'CHECK_EXPIRATION'.
11

That needs to be ON (or OFF)

1 Like
12

Yay! You're my hero!

After changing both NOs to OFFs, it worked!