August 24, 2010 at 2:56 pm
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.
August 24, 2010 at 3:35 pm
Generally, you would assign someone to one role. Specifically, the roles "datareader" and "denydatareader" counter each other.
Even better is to create a new role, and assign the specific permissions to that role. Then, add users to the role.
August 24, 2010 at 9:39 pm
USE [demodb]
GO
CREATE USER [testme] FOR LOGIN [testme]
GO
USE [demodb]
GO
EXEC sp_addrolemember N'db_accessadmin', N'testme'
GO
USE [demodb]
GO
EXEC sp_addrolemember N'db_backupoperator', N'testme'
GO
USE [demodb]
GO
EXEC sp_addrolemember N'db_datareader', N'testme'
GO
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply