Add user roles and authorizations in SQL Server2008

1. Adding User Roles

To successfully access data in a SQL Server database, we need two aspects of authorization:

1. Get permission to connect to a SQL Server server; it's like we got a right to get into a building.

2. Gain access to data in a particular database (SELECT, Update, delete, CREATE TABLE ...) ); it's like we got the key to a room in the building.

Suppose we are going to set up a DBA database account to manage database mine.

1. Start the login account at the SQL Server server level (create login)

--Creating login account (Create login) Create login DBA with password= ' 1234 ', default_database=mine

The login account name is: "DBA", Login password: "1234", the default connection to the database: "Mine". At this point, the DBA account can connect to the SQL Server server. However, it is not possible to access objects in the database at this time (strictly speaking, the DBA account defaults to the guest database user identity and can access the database objects that the guest can access).

To enable the DBA account to access the objects it needs in the mine database, you need to establish a "database user" in the database mine, give this "database user" some access rights, and map the login account "DBA" to this "database user". In practice, the name "database user" is the same as the name of "login account", i.e. "DBA". Creating a database user and establishing a mapping relationship can be done in just one step:

2. Creating a database User (create user):

-Create a database user for the login account, and under user in security in the MyDB database, you can find the newly created dbacreate User DBA for login DBA with default_schema=dbo

and specifies that the default schema for the database user "DBA" is "dbo". This means that the user "DBA" executes "SELECT * from T" and actually executes "select * from DBO.T".

3. Give database User "DBA" permission by joining database role

--By adding a database role, give the database user "db_owner" permission exec sp_addrolemember ' db_owner ', ' DBA '

At this point, the DBA has full control over the objects in the database mine.

If you want the SQL Server login account "DBA" To access multiple databases, such as Mine2. You can have the SA execute the following statement:

--Let SQL Server login account "DBA" Access multiple databases use Mine2gocreate user DBA for login dba with default_schema=dbogoexec sp_addrolemember ' db _owner ', ' DBA ' Go

At this point, the DBA can have two database mine, MINE2 administrative rights!

The difference between revoke and deny

REVOKE: Revoke the permissions granted before

Deny: Denies granting permissions to the security account in the current database and prevents the security account from inheriting permissions through its group or role memberships. For example, UserA's role group has inset permissions, but we deny usera that it does not have insert permissions, then even if UserA to another role group that contains the insert, there is no insert permission, unless the user is shown authorization.

In simple terms, deny is not allowed in the future, revoke is to take back what has been given.

GRANT insert on TableA to roleagoexec sp_addrolemember Rolea, ' UserA '-user UserA will have TableA insert permission Gorevoke insert on TableA From Rolea--user UserA will not have TableA insert permission, take back permissions gogrant insert on TableA Torolea--re-give Rolea with TableA insert permission go DENY insert on TableA to UserA--although the user UserA Rolea has the TableA insert permission, but UserA itself is denied, so the user UserA will have no TableA insert permissions.

Reference: http://database.51cto.com/art/201009/224075.htm

Add user roles and authorizations in SQL Server2008