Database roles apply separately for each database. A database role can be
assigned to any Windows NT user or group, or SQL user or group.
The following database roles are available:
| Database Role |
Permissions |
| db_owner |
Can perform any activity. |
| db_accessadmin |
Can manage users and user groups. |
| db_datareader |
Can read data from a database. |
| db_datawriter |
Can add, edit and delete data in a database. |
| db_ddladmin |
Can add, edit and delete database objects. |
| db_securityadmin |
Can manage roles and permissions. |
| db_backupoperator |
Can make backups. |
| db_denydatareader |
Cannot read data in a database. |
| db_denydatawriter |
Cannot edit data in a database. |
Assinging Database Roles with Enterprise Manager
- In Enterprise Manager, find the desired SQL Server instance.
- Go to Databases and expand the tree of the desired database.
- Go to Roles.
- In the right-hand panel, right-click the role you wish to assing and
select Properties.
- Click the Add… button and select the users you wish to assign
the selected role to. Note that you can select only users of the
selected database.
Assinging Database Roles with T-SQL
Adding Users
To add a user, use the command
sp_addrolemember [@rolename =] 'role', [@membername =] 'security_account'
[@rolename =] 'role'
|
The name of the server role in the current
database. |
[@membername =] 'security_account'
|
The account that should be added to the role. It can be any
valid SQL Server user, SQL Server role, Windows NT user or group
with access to the current file.
When adding Windows NT groups or users, specify the name that is
known to the file (added with sp_grantdbaccess). |
Removing Users
To remove a user, use the command
sp_droprolemember [@rolename =] 'role', [@membername =] 'security_account'
[@rolename =] 'role'
|
The name of the role from which the user is to
be removed. |
[@membername =] 'security_account'
|
The account that should be removed from the role. It can be any
valid SQL Server user, SQL Server role, Windows NT user or group
with access to the current file.
When removing Windows NT groups or users, specify the name that is
known to the file (added with sp_grantdbaccess). |
Adding Accounts
To grant access to the current file, use the command
sp_grantdbaccess [@loginame =] 'login' [,[@name_in_db =] 'name_in_db' ]
[@loginame =] 'login'
|
The login for the new account. Windows NT
logins must include the domain name (e.g. domain\login). |
[@name_in_db =] 'name_in_db'
|
The name of the new account. It must be a unique name. |
Removing Accounts
To revoke access to the current file, use the command
sp_revokedbaccess [@name_in_db =] 'name'
[@name_in_db =] 'name'
|
The account for which access should be revoked.
When removing Windows NT groups or users, specify the name that is
known to the file (added with sp_grantdbaccess). |
Show Configuration
xp_loginconfig ['config_name']
'config_name'
|
The name of the parameter that should be shown. If empty, the
whole configuration will be shown. |