CREATE LOGIN syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CREATE LOGIN syntax

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-08 : 05:37:11

Hi,

I have the code below, my CREATE LOGIN statement seems to be generating an error, what is the correct syntax?

Incorrect syntax near 'test1234'.

Thanks


if not exists(select dbid from master..sysdatabases where name = @dbName)
-- CREATE SERVER LOGIN
EXEC ('CREATE LOGIN ' + @dbUsername + ' WITH PASSWORD = ' + @dbPassword)
-- CREATE DATABASE
EXEC ('CREATE DATABASE ' + @dbName)
-- CREATE DB USER
EXEC ('USE ' + @dbName)
EXEC ('CREATE USER ' + @dbUsername + ' FOR LOGIN ' + @dbUserName + ' WITH DEFAULT_SCHEMA = db_owner')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 05:39:19

try printing the sql strings using PRINT () and see for any syntax errors before excuting them with EXEC()

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-08 : 05:48:23

This is what is being output

CREATE LOGIN testUser WITH PASSWORD = Password1234
CREATE DATABASE dbTest
USE dbTest
CREATE USER testUser FOR LOGIN testUser WITH DEFAULT_SCHEMA = db_owner

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 06:04:20

[code]if not exists(select dbid from master..sysdatabases where name = @dbName)
-- CREATE SERVER LOGIN
EXEC ('CREATE LOGIN ' + @dbUsername + ' WITH PASSWORD = ''' + @dbPassword+'''')
-- CREATE DATABASE
EXEC ('CREATE DATABASE ' + @dbName)
-- CREATE DB USER
EXEC ('USE ' + @dbName)
EXEC ('CREATE USER ' + @dbUsername + ' FOR LOGIN ' + @dbUserName + ' WITH DEFAULT_SCHEMA = db_owner')[/code]

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-08 : 06:16:12

Thanks!

Its working fine, apart from the last line - it creates the user in the current database, I want to create it in the new database I just created - it seems the USE @dbName doesn't work?

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-08 : 06:26:07

The use works, but the USE is only effective within that EXEC.

Try
EXEC ('USE ' + @dbName + '; CREATE USER ' + @dbUsername + ' FOR LOGIN ' + @dbUserName + ' WITH DEFAULT_SCHEMA = db_owner')

--
Gail Shaw
SQL Server MVP

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-08 : 06:37:26

That works great, thanks.

One last question, i've added this

-- ADD TO ROLES
EXEC sp_addrolemember 'db_owner', @dbUsername

How can I get that to work with the newly created DB?

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-08 : 08:16:29

Same as the create user

EXEC ('USE ' + @dbName + '; CREATE USER ' + @dbUsername + ' FOR LOGIN ' + @dbUserName + ' WITH DEFAULT_SCHEMA = db_owner; exec sp_addrolemember 'db_owner', ''' + @dbUsername + '''')

--
Gail Shaw
SQL Server MVP

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-08 : 09:35:16

But this time i'm calling another procedure - would it be like this? (i.e. Nested EXEC's?)

EXEC('USE ' + @dbName +'; EXEC sp_addrolemember ''db_owner''', ' + @dbUsername)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 09:42:31

quote:
Originally posted by Mondeo

But this time i'm calling another procedure - would it be like this? (i.e. Nested EXEC's?)

EXEC('USE ' + @dbName +'; EXEC sp_addrolemember ''db_owner''', ''' + @dbUsername+'''')

should be like above

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-08 : 09:53:33

I forgot to escape the quotes around the dbowner, otherwise, how I had it will work. No need for a second exec.

EXEC ('USE ' + @dbName + '; CREATE USER ' + @dbUsername + ' FOR LOGIN ' + @dbUserName + ' WITH DEFAULT_SCHEMA = db_owner; EXEC sp_addrolemember ''db_owner'', ''' + @dbUsername + '''')

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-08 : 09:55:35

quote:
Originally posted by visakh16

quote:
Originally posted by Mondeo

But this time i'm calling another procedure - would it be like this? (i.e. Nested EXEC's?)

EXEC('USE ' + @dbName +'; EXEC sp_addrolemember ''db_owner''', ''' + @dbUsername+'''')

should be like above

One too many quotes after the db_owner. Highlighted quote shouldn't be there.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources