:::: MENU ::::

Posts Categorized / Security and Permissions

  • Nov 05 / 2008
  • 0
dbDigger, Logins and Users, Security and Permissions

Configure Database State and restrict access in SQL Server

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.

Database State (DB_STATE_OPTION)
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.

  • Nov 05 / 2008
  • 0
dbDigger, Security and Permissions, T-SQL Enhancements

Trustworthy (TRUSTWORTHY) in SQL Server 2005

Trustworthy option is used to control access to resources outside the database. When trustworthy is set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, database modules like user-defined functions and stored procedures that use an impersonation context can access resources outside the database. When Trustworthy is set to FALSE, database modules that use an impersonation context cannot access resources outside the database.
  • Nov 05 / 2008
  • 0
dbDigger, Security and Permissions, System Stored Procedures

Cross-Database Ownership Chaining Enabled (DB_CHAINING) in SQL Server

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.

  • Oct 30 / 2008
  • 0
dbDigger, Logins and Users, Security and Permissions

Securables in SQL Server 2005

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

  • server
  • database
  • schema

Securable scope Server contains following securables on SQL Server 2005

  • Endpoint
  • Login
  • Database

Securable scope database contains the following securables on SQL Server 2005

  • User
  • Role
  • Application role
  • Assembly
  • Message Type
  • Route
  • Service
  • Remote Service Binding
  • Fulltext Catalog
  • Certificate
  • Asymmetric Key
  • Symmetric Key
  • Contract
  • Schema

Securable scope schema contains the following securables on SQL Server 2005

  • Type
  • XML Schema Collection
  • Object

SQL Server Objects
The following securables are members of the object class for SQL Server 2005

  • Aggregate
  • Constraint
  • Function
  • Procedure
  • Queue
  • Statistic
  • Synonym
  • Table
  • View
  • Oct 06 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Get list of all possibble permissions for a SQL Server 2005 object

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.

Consult us to explore the Databases. Contact us