In the SINGLE mode only one user is permitted to access the database at a time. In the RESTRICTED mode only members of the db_owner, dbcreator, and sysadmin roles can connect to the database.
Database Read-Only (READ_ONLY or READ_WRITE)
You can use Database Read-Only option to control whether updates are allowed on the database. When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, users can only read from the database and are not permitted to modify data. When set to FALSE, the read and write operations are permitted on the database. To change the state of this option, you must have exclusive access to the database.
You can use Database State option to control the state of the database. When set to NORMAL, the database is fully operational and available for use. When the database is set to CLOSED, the database is shut down cleanly and marked offline. The database cannot be accessed or modified while in this state. When set to EMERGENCY, the database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. The EMERGENCY database state is used primarily for troubleshooting purposes.
Restrict Access (DB_USER_ACCESS_OPTION)
Restrict Access can be used this option to control access to the database. In MULTIPLE mode all users are allowed to connect to the database as long as they have the appropriate permissions. Conversely, in the SINGLE mode only one user is permitted to access the database at a time. In the RESTRICTED mode only members of the db_owner, dbcreator, and sysadmin roles can connect to the database.
Cross-Database Ownership Chaining Enabled is a security feature that controls database access by external resources, such as objects from another database. When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, the database can be the source or target of a cross-database ownership chain. Setting the Cross-Database Ownership Chaining to FALSE prevents participation in cross-database ownership chaining.
This option is effective only when the instance wide cross db ownership chaining server option is set to 0.
When the cross db ownership chaining option is enabled via SQL Server Management Studio or set to 1 via the sp_configure stored procedure, this option is ignored and all databases in the server can participate in cross database ownership chaining.
Database objects aren’t the only things that you can secure. You can create rights and privileges for everything from the server itself to each database and each object that they contain. Securables in SQL Server 2005 are the resources to which the SQL Server Database Engine authorization system regulates access. Some securables can be contained within others, creating nested hierarchies called “scopes” that can themselves be secured. You can secure at server level, database level and schema level. The securable scopes are
Securable scope Server contains following securables on SQL Server 2005
Securable scope database contains the following securables on SQL Server 2005
- Application role
- Message Type
- Remote Service Binding
- Fulltext Catalog
- Asymmetric Key
- Symmetric Key
Securable scope schema contains the following securables on SQL Server 2005
- XML Schema Collection
SQL Server Objects
The following securables are members of the object class for SQL Server 2005
It is interesting question that how to get all the possible permissions for SQL Server 2005 object. The object may be any table, view, function. Apart from these objects you could even get list of all possible permissions for your database or server.
Format for this T-SQL script is
USE DBNameHere GO SELECT * FROM fn_my_permissions('ObjectName', 'type'); GO
In above script provide DB name first. If you want to generate possible permissions for a database or server itself then provide NULL as objectname and write ‘database’ or ‘server’ as type parameter. For example to get possible server level permissions for server itself we will have following syntax
SELECT permission_name FROM fn_my_permissions(null, 'server'); GO
Similarly to get possible permissions for AdventureWorks database we have following syntax
USE AdventureWorks GO SELECT permission_name FROM fn_my_permissions(null, 'database'); GO
Here are some examples to get list of all possible permissions for SQL Server objects like view, table, database and server.
To get list of all possible permissions for a view [purchasing.object] in AdventureWorks database
USE AdventureWorks GO SELECT * FROM fn_my_permissions('purchasing.vVendor', 'object'); GO
Similarly to get list of all possible permissions for a table [production.product] in AdventureWorks database
USE AdventureWorks; GO SELECT * FROM fn_my_permissions('production.product', 'object'); GO
Above commands will provide you all permissions/privileges list. you may choose among these as required.