Create logins script

Question

Post reply

Create logins script

Imke Cronje

SSCarpal Tunnel

Points: 4826
More actions
July 2, 2008 at 2:30 am

#194604

Hi Guys,
I have a create login script. See below:
CREATE LOGIN [TestUser] WITH PASSWORD=N'TestUser1', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Can I use a script to specify dbowner rights to a particular database? If so, what script can I use.
Regards,
IC

Viewing 9 posts - 1 through 8 (of 8 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

You can, on 2005 try something along the lines of:

USE particularDatabase

GO

CREATE USER TestUser FOR LOGIN [TestUser]

GO

EXEC sp_addrolemember N'db_owner', N'TestUser'

GO

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 2

Thanks...just one problem....The script below doesn't work:

EXEC sp_addrolemember N'db_owner', N'TestUser'

I will keep looking.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

Imke Cronje (7/2/2008)
Thanks...just one problem....The script below doesn't work:
EXEC sp_addrolemember N'db_owner', N'TestUser'
I will keep looking.

Could you tell me how it does not work? I.e. is there an error message? If so, what is it. Or maybe it does not do what you would like it to do?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 4

it worked the first time when I used it...i deleted the user, recreated the user again, when I execute the command it says "Command Completed Successfully" but it does not give the user the db_owner rights.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

Imke Cronje (7/2/2008)
it worked the first time when I used it...i deleted the user, recreated the user again, when I execute the command it says "Command Completed Successfully" but it does not give the user the db_owner rights.

Could you double check the database in which you are executing these statements and the database in which you are checking them. The above statements work on the current database, so the users are created in a particular database, and are added to a group in a particular database.

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

anjan.ashok SSCrazy Points: 2783 More actions · Answer 6

After creating user

assign db_owner right using sql management studio by using sa login

no need of script its easy

just double click on logins-> click on concerned user and change the permission.

😉

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 7

Thanks..i already know that....i have always done it manually...i ran the script below and it basically solved the problem:

sp_change_users_login

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 8

Imke Cronje (7/2/2008)
Thanks..i already know that....i have always done it manually...i ran the script below and it basically solved the problem:
sp_change_users_login

If you are familiar with the UI already, you could create the scripts with the UI first. Just make the changes in the UI in Management Studio, and usually there is a Script button there that will show you the commands Management Studio would execute 🙂 Then you can just reuse the scripts.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software