Methods for creating read-only users in SQL Server 2008 databases _mssql2008

In SQL Server 2008, in order to protect the security of the database, the need for different users to open different access user, then how to simply control the user's permissions? Below we will create a read-only user, for everyone to learn to use.

SQL Server 2008

Microsoft SQL Server Management Studio

1. First open "Microsoft SQL Server Management Studio" and log on with the administrator account. Here I choose the server is "local", the account is "Windows Authentication", if it is connected to a remote server, enter the remote server address.

2, find "security", open, you can see "login name", the right button above.

3, select "New login name"

4, in the "General" option, enter the login name, select the authentication method. I chose SQL Server authentication here, and I ticked off the "Enforce password policy."

5, on the right side of the User Mappings tab, the user mapped to this logon name selects the database that the user can manipulate. For each database, in the "Database role membership" below, select "Db_datareader"

6, so, a read-only user is established, with this user logged on, only the mapped database for read-only access.

SqlServer2008 Add read-only user view stored procedure permissions

Zetsche said yesterday, the general enterprise will set the corresponding database of the corresponding rights account, and when we set only to some users authorized read-only permission (that is, public), these users may need to view the SQL stored procedures, and therefore need to authorize it.

The first step: Use the SA user to enter the database, in the security to find the user right key-Properties

Step Two: Find the Security object-click Search

Step three: Locate the server name in the pop-up window

Step Fourth: In the following selection box to find "view arbitrary definition" on the hook to save, and restart the database service, MSSQLSERVER, reboot after the note to start the SQL agent, because the restart MSSQLServer will stop it.

How to create a read-only account in SQL Server

1. Enter SQL Server Management Studio
2, select Security-> login-> right button new login name
3, in the regular input user name and password
4, in the user mapping "map to this login user" to select the user can operate the database
5, in the "Database role membership" in the choice of "db_datareader",
6, such a read-only user to create the completed.

Server role Reference (can operate on the database)

Bulkadmin can run BULK INSERT statement BULK INSERT Detail http://www.jb51.net/article/70667.htm
dbcreator Create, modify, delete, restore any database
Diskadmin Manage disk files
Processadmin can terminate a program running in the instance of the database engine
Securityadmin can manage logins and their properties, have Grant,deny, and revoke server and database-level permissions, and reset the password for SQL Server logins
ServerAdmin can change server-wide configuration options and shut down the server
Setupadmin can add and remove linked servers and perform stored procedures (such as sp_serveroption) for certain systems that can perform
SysAdmin does any activity in SQL Server that crosses all other fixed server roles, and by default all members of the Windows Builtin\admin Group (local Administrators group) are sysadmin

Members of the fixed server role

User Mapping Reference (you can manipulate the data in the database)

DB_Owner users who can perform all of the technical actions in the database
Db_accessadmin can add, delete user's user
Db_datareader users who can view data in a user table in all databases
Db_datawrite can add, modify, and delete data from all database user tables
Db_ddladmin users who can perform DDL operations in the database, the creation and management of the DDL (Data Definition Language) datasheet
Db_securityadmin can manage users in a database for all actions related to security permissions
Db_backoperator users who can back up the database (you can publish DBCC and CHECKPOINT statements, which are typically used before a backup)
Db_denydatareader users who cannot see any data in the database
Db_denydatawrite users who cannot modify any data in the database