User Mapping - 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

General SQL Server Forums

New to SQL Server Administration

User Mapping

Author

Topic

jadeite100
Starting Member

5 Posts

Posted - 2010-08-24 : 16:46:13

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 membershi 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] DISABLE

Yours,

Frustrated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources