Script a user to a specific database?

Question

I am working on my SQL deployment script.

I have a login named "User123", I have added to my current database, but I have a stored procedure that logs information to MyDB, I need User123 permissions at MyDB so I created a user at MyDB with permissions, however how do I script this rather than adding the user manually..

I have this so far, but cant seem to figure out how to add it to the other database, "MyDB" can be hard coded as there will only be 1 on each server, however i cant hard code the current database im running script on

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'User123')
BEGIN
   CREATE USER [User123] FOR LOGIN [User123] WITH DEFAULT_SCHEMA=[dbo]
END
GO

This only runs on current database, how do I make it run against other db.

Example..

MyDB
Security
User123

DB1
Security
User123

i want a script that will add the user to both current database and "MyDB", without having to manually switch databases

I usually use INSERT INTO sys.database_pricipals WHERE name=N'user123' just put the insert into line twice, once for each database? maybe i misunderstand the question? — Johnny Craig
@JohnnyCraig: Wouldn't the same USE <DB> still be relevant? Or at least the INSERT would have to be fully qualified - ie. INSERT INTO MyDB.sys.database_principals... But the question is whether all fields are properly set when you use the INSERT-method. Have you validated this? — user806549

Unknown Unknown · Accepted Answer · 2011-12-20T23:09:16

What about:

USE MyDB
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'User123')
BEGIN
   CREATE USER [User123] FOR LOGIN [User123] WITH DEFAULT_SCHEMA=[dbo]
END
GO

USE DB1
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'User123')
BEGIN
   CREATE USER [User123] FOR LOGIN [User123] WITH DEFAULT_SCHEMA=[dbo]
END
GO

Script a user to a specific database?

1 Answers