LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » SQL Server Security » User Mapping

User Mapping

Options

Previous Topic · Next Topic

jadeite100

Posted: Tuesday, August 24, 2010 4:04:18 PM

Rank: Newbie

Joined: 8/24/2010
Posts: 4
Points: 12

Hi All:

I am using Microsoft Sql 2005. In "Microsoft Sql Server Management Studio" when I clicked on the Security icon and click on Logins icon it gives me a list of user logins. For example for user login test2, when I right click on it and choose Properties. When I choose "User Mapping". I get the user "test2" Map to database "FORMS" and also in the below section called "Database role membership for: FORMS". It is mapped to the following role membership:

db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_owner
public

Does anyone know how to create a sql script that will do that.
When I right click the user "test2" and choose "script login as>create to > new query editor window>. It generates the script to create the user "test2" with the "server roles" mapped to it but not the "user mapping".

Here is the script being generated:
CREATE LOGIN [test2] WITH PASSWORD=N'test2', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'sysadmin'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'securityadmin'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'serveradmin'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'setupadmin'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'processadmin'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'diskadmin'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'dbcreator'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'test2', @rolename = N'bulkadmin'
GO
ALTER LOGIN [test2] ENABLE
Yours,

Frustrated.

Scott Whigham

Posted: Tuesday, August 24, 2010 4:11:55 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

I don't have a SQL 2005 instance to place with ATM but try this:

1. Right click on the FORMS database and go to Tasks -> Generate SQL Scripts

2. Choose to script out the Logins

That should do it but I'm going from memory...

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.