Pause or Resume a Database Mirroring Session

We can pause and resume any Database Mirroring any time. Pausing mirroring preserves the mirroring state. Sometime pausing is very useful to improve the performance of the principal server. Pausing sets the state of the mirroring session to SUSPENDED, and the mirror database no longer updated with the principal database. 

We should resume the mirroring session quickly because as long as the session is paused then transaction log cannot be truncated. If the session is paused for a long time the log file may be grow large causing the database unavailable.

When a session is paused the  principal database is remains available.

Steps to pause and resume a database mirroring session follow below steps:

1. In Object explorer, connect to the principal server and click the server name to expand.
   
   
2. click on the Database and select the database.
   
   
3. Now right click the database, select Tasks, and then click on Mirror. Now, it will opens the Mirroring page of the Database Properties dialog box.
   
   
4. To pause the session, click Pause.
   A Confirmation message will come, if you click Yes, the session is paused, and the button changes to Resume.
   
   For more information about the impact of pausing a session, see Pausing and Resuming Database Mirroring.
5. To resume the session, click Resume.
   
   Ref: MSDN

Number to Word Function

CREATE FUNCTION NumToWords_Ver2(@num numeric)
RETURNS varchar(1000)
AS


BEGIN
DECLARE @cNum varchar(20)
SET @cNum = @num
DECLARE @len int
SET @len = LEN(@num)
DECLARE @place int
SET @place = 0
DECLARE @digit varchar(1), @tens varchar(2)
DECLARE @res varchar(1000)
DECLARE @nullStr int
SET @res = ''
DECLARE @tblNum TABLE(Num int, NumStr varchar(20))
INSERT INTO @tblNum
SELECT 1, ' One' UNION
SELECT 2, ' Two' UNION
SELECT 3, ' Three' UNION
SELECT 4, ' Four' UNION
SELECT 5, ' Five' UNION
SELECT 6, ' Six' UNION
SELECT 7, ' Seven' UNION
SELECT 8, ' Eight' UNION
SELECT 9, ' Nine' UNION
SELECT 10, ' Ten' UNION
SELECT 11, ' Eleven' UNION
SELECT 12, ' Twelve' UNION
SELECT 13, ' Thirteen' UNION
SELECT 14, ' Fourteen' UNION
SELECT 15, ' Fifteen' UNION
SELECT 16, ' Sixteen' UNION
SELECT 17, ' Seventeen' UNION
SELECT 18, ' Eighteen' UNION
SELECT 19, ' Nineteen' UNION
SELECT 20, ' Twenty' UNION
SELECT 30, ' Thirty' UNION
SELECT 40, ' Fourty' UNION
SELECT 50, ' Fifty' UNION
SELECT 60, ' Sixty' UNION
SELECT 70, ' Seventy' UNION
SELECT 80, ' Eighty' UNION
SELECT 90, ' Ninety'
DECLARE @hundred varchar(200)
SET @hundred = ''
DECLARE @nStr varchar(20)
SET @place = @len
WHILE @place > 0


BEGIN
SET @place = @place - 1
SET @nStr = NULL
SET @digit = SUBSTRING(@cNum, @len-@place, 1)
IF (@place+1) % 3 = 1 --One's place
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
IF @nStr IS NOT NULL
SET @hundred = @nStr
IF LEN(@hundred) > 0
SET @res = @res + @hundred + CASE @place / 3
WHEN 0 THEN ''
WHEN 1 THEN ' Thousand'
WHEN 2 THEN ' Million'
WHEN 3 THEN ' Billion'
WHEN 4 THEN ' Trillion'
WHEN 5 THEN ' Qwadrillion'
WHEN 6 THEN ' Quintillion'
END
SET @hundred = ''
END
IF (@place+1) % 3 = 0 --Hundred's place
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
IF @nStr IS NOT NULL
SET @hundred = @nStr + ' Hundred'
SET @tens = SUBSTRING(@cNum, @len-@place+1, 2)
IF LEN(@hundred) > 0 AND (@tens = '' OR @tens = '00')
SET @res = @res + @hundred + CASE (@place+1) / 3
WHEN 0 THEN ''
WHEN 1 THEN ''
WHEN 2 THEN ' Thousand'
WHEN 3 THEN ' Million'
WHEN 4 THEN ' Billion'
WHEN 5 THEN ' Trillion'
WHEN 6 THEN ' Qwadrillion'
WHEN 7 THEN ' Quintillion'
END
ELSE
SET @res = @res + @hundred
SET @hundred = ''
END
ELSE IF (@place+1) % 3 = 2 --Ten's place
BEGIN
SET @tens = SUBSTRING(@cNum, @len-@place, 2)
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @tens
IF @nStr IS NULL
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit * 10
SET @digit = SUBSTRING(@cNum, @len-@place+1, 1)
SELECT @nStr = @nStr + NumStr FROM @tblNum WHERE Num = @digit


END
SET @hundred = @nStr
IF LEN(@hundred) > 0
SET @res = @res + @hundred + CASE (@place+1) / 3
WHEN 0 THEN ''
WHEN 1 THEN ' Thousand'
WHEN 2 THEN ' Million'
WHEN 3 THEN ' Billion'
WHEN 4 THEN ' Trillion'
WHEN 5 THEN ' Qwadrillion'
WHEN 6 THEN ' Quintillion'
END
SET @place = @place - 1
SET @hundred = ''
END


END
RETURN @res
END

Not associated with a trusted SQL Server connection

When you're setting up a new MS-SQL connection, you might get the following error:
Not associated with a trusted SQL Server connection


What this means is that you have a logon that was defined in MSSQL - but you also have MSSQL set to only use Windows logon accounts.


To fix this, load up the SQL management tool. Expand out the SQL Server Group. When you click on SQL Server Group in the left window, it'll show an entry in the right window. Click on that and get its properties. Under "security", set authentication to allow SQL Server logins.

Errors after Running DBCC Checkdb Command

In an MSSQL corruption problem, you might see error messages after running DBCC Checkdb command. Such situations render MDF files inaccessible, which can cause grave data loss in organization that might even halt some important operations. For recapturing proper execution of work again, you must invest in a high-performing, reliable and result-oriented MSSQL repair tool. One such tool is SysTools SQL recovery software.

MSSQL Server: MSSQL Server is a highly reliable Microsoft product. SQL (Structured Query Language) is a database Server application used by most of the organizations to manage a lot of crucial data properly. It is a useful database management tool that helps to query databases and to manage information stored in already constructed databases. The information in MSSQL Server is saved in files with .mdf file extension.
MSSQL Corruption: At times, SQL MDF files can get corrupted due to some unforeseen and uninvited reasons. At this time, the search begins for an MSSQL database repair solution.
Errors after Running DBCC Checkdb Command: When there is an MSSQL corruption problem, you might come across error messages after running DBCC Checkdb command. Some of the common error messages that emerge on your computer screen after such corruption are as follows:
• Table error: Object ID 0, index ID 0, page ID (1:105). The PageId in the page header = (0:0).
• File: , line=466 Failed Assertion = '0'.
• Table Corrupt.
• Conflict occurred in database‘db_name’, table 'table_name', column 'column_name'
• Memory or buffer error, space provided to read column is too small.
• Internal errors
Repair MSSQL Database – Bring Back Proper Functioning of Work: Such situations of MSSQL corruption render MDF files inaccessible, which can create grave data loss scenarios in the organization that might even halt some of the important operations completely. For recapturing proper execution of work again, you must invest in an MSSQL repair tool. Such corruption problems are required to be resolved instantly to carry out all operations appropriately again and this signifies the importance of SQL database repair tools.
SQL Server Database Recovery: Using a third-party software tool to repair SQL corrupted MDF files and to recover MSSQL database elements is an ideal way to approach such problematic corruption situation. This is because an outside tool would ensure a quick, efficient and professional way to repair corrupt SQL database, provided you choose a high-performing, reliable and result-oriented MSSQL restore database tool. One such tool is SysTools SQL recovery software. This software is not just efficient, well-performing, result-yielding; but also, easy-to-use at the same time. This simple and intuitive MSSQL database repair solution guarantees you high-end results and great recovery outcomes. SysTools SQL recovery software supports most SQL Server versions. So, if you are an SQL 2000 user, then this product can be an apt answer to your question – “How to repair MSSQL 2000 database?”


ArticleSource: ArticlesAlley.com

WHERE clause and HAVING clause

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function


Where clause is used to filter records, having is used to filter groups.


Use HAVING instead of WHERE when you want to establish a condition that involves a grouping (aggregating) function. (Grouping functions are things like count(), max(), sum(), etc.)



The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.


The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.

Delete Duplicate Rows

Here I have used CTE and ROW_NUMBER to delete duplicate records.

/* Delete Duplicate Records */
WITH CTE (Col1, DuplicateRowCount)
AS
(SELECT Col1, ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col1) AS DuplicateRowCountFROM DuplicateRcordTable)
DELETE FROM CTE WHERE DuplicateRowCount > 1 The above command will delete duplicate record in table i.e DuplicateRcordTable. which have duplicate record of column Col1 in table.

Remove Database Mirroring

1. During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.
   
   
2. Expand Databases, and select the database.
   
   
3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
   
   
4. In the Select a Page pane, click Mirroring.
   
   
5. To remove mirroring, click Remove Mirroring. A prompt asks for confirmation. If you click Yes, the session is stopped and mirroring is removed from the database.
   
   For more information about the impact of removing mirroring, see Removing Database Mirroring.
   
   
6. Optionally, you can recover the former mirror database. On the server instance that was the mirror server, use the following Transact-SQL statement:
   
   RESTORE DATABASE <DB_Name> WITH RECOVERY
   

Error: 1418 – Microsoft SQL Server – The server network address can not be reached or does not exist. Check the network address name

The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.

Solution:

Step 1. Your system Firewall should not block SQL Server port.

Step 2. Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.

Just to make sure follow one more step which may or may not be necessary.

Step 3. Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Client Configuration
Enable TCP/IP protocol.

Rebuild all indexes for all tables and all databases

Below is script to rebuild all indexes for all tables of all Databases. In this script I had used two cursor, one for Table and other for Database. In this script I had excluded system Databases. You can exclude some other database also in the same way.


DECLARE @DB_Name VARCHAR(255)   
DECLARE @Table_Name VARCHAR(255)  
DECLARE @Command NVARCHAR(500)  
DECLARE @FillFactor INT 


SET @FillFactor = 90 


DECLARE DBCursor CURSOR FOR  
SELECT Name FROM MASTER.dbo.SysDatabases   
WHERE Name NOT IN ('master','msdb','tempdb','model','distribution')   
ORDER BY 1  


OPEN DBCursor  


FETCH NEXT FROM DBCursor INTO @DB_Name  
WHILE @@FETCH_STATUS = 0  
BEGIN  


   SET @Command = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 
  table_name + '']'' as tableName FROM ' + @DB_Name + '.INFORMATION_SCHEMA.TABLES 
  WHERE table_type = ''BASE TABLE'''   


   -- create table cursor  
   EXEC (@Command)  
   OPEN TableCursor   


   FETCH NEXT FROM TableCursor INTO @Table_Name   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   


       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command 
           SET @Command = 'ALTER INDEX ALL ON ' + @Table_Name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@FillFactor) + ')' 
           EXEC (@Command) 
       END
       ELSE
       BEGIN
          -- SQL 2000 command 
          DBCC DBREINDEX(@Table_Name,' ',@FillFactor)  
       END


       FETCH NEXT FROM TableCursor INTO @Table_Name   
   END   


   CLOSE TableCursor   
   DEALLOCATE TableCursor  


   FETCH NEXT FROM DBCursor INTO @DB_Name  
END  
CLOSE DBCursor   
DEALLOCATE DBCursor

Collation Conflicts in a SQL Server Join

Today I got an error while running this query : 
SELECT TOP 20 * FROM AccountMaster AS A INNER JOIN GroupMaster AS G ON A.GroupName = G.GroupName
I got this Error:

I searched the net since I couldn’t remember the exact syntax for the clause. 

The solution is easy, add a COLLATE DATABASE_DEFAULT to the join condition to force a specific collation on the field. I could easily have added a COLLATE Latin1_General_CI_AS as well, but since I knew that the second field was database defaults, I did this:

SELECT TOP 20 * FROM AccountMaster AS A INNER JOIN GroupMaster AS G ON A.GroupName COLLATE DATABASE_DEFAULT = G.GroupName

Now this query worked fine.

List tables which are dependent on a given table

You can check table dependencies in many ways.
Option 1: 
Right-click on a table and choose 'View Dependencies'.

Option 2: 
if you want to do it programmatically check out the below code snippet
Select S.[name] as 'Dependent_Tables'
From sys.objects S inner join sys.sysreferences R 
on S.object_id = R.rkeyid
Where S.[type] = 'U' AND R.fkeyid = OBJECT_ID('Person.StateProvince')

Primary keys without Clustered Index

As we know, by default if we create a Primary Key (PK) field in a table it creates a Clustered Index automatically. Someone had asked me "Is there a way to create Primary Key (PK) field without clustered Index?”. There are three methods by which we can achieve this. 

Method 1:

Using this method we can specify it while creating the table schema itself.
Create Table Test_Table
(
Field1 int Identity not null primary key nonclustered,
Field2 nvarchar(50),
Field3 int null
)
Go
Method 2:
Using this method also we could specify it while creating the table schema. It just depends on your preference.
Create Table Test_Table
(
Field1 int Identity not null,
Field2 varchar(30),
Field3 int null
Constraint pk_parent primary key nonclustered (Field1)
)
Go
Method 3:
If at all you already have a table which have a clustered index on a PK field then you might want to opt for this method.
Step 1: Find the constraint name 
sp_helpconstraint Test_Table 
/*
This way we could find out the constraint name. Let’s assume that our constraint name is PK_Test_Table_Field1
*/
Step 2: First drop the existing constraint
Alter table Test_Table drop constraint PK_Test_Table_Field1
Step 3: Add the new nonclustered index to that field now
Alter table Test_Table add constraint PK_parent1 primary key nonclustered (Field1)

Disable All Constraints or Triggers in a Single Command

--Disable Constraints 

EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
--Disable Triggers 

EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 
-- Load data Now

--Enable Constraints 

EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
--Enable Triggers 

EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 

Database level permissions for SQL Server 2005 and 2008

SQL Server 2005 introduced a new concept to SQL Server security and permissions: 

securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the database.

Below are the list of database-level permissions:

Permission	Effect
ALTER	This grants or denies the ability to alter the existing database.
ALTER ANY APPLICATION ROLE	This grants or denies the ability to create, drop, or alter application roles. The db_securityadmin fixed database role has this permission implicitly.
ALTER ANY ASSEMBLY	This grants or denies the ability to create, drop, or alter CLR assemblies. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY ASYMMETRIC KEY	This grants or denies the ability to create, drop, or alter asymmetric keys for encryption. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY CERTIFICATE	This grants or denies the ability to create, drop, or alter certificates for encryption. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY CONTRACT	This grants or denies the ability to create and drop contracts for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY DATABASE DDL TRIGGER	This grants or denies the ability to create, drop, or alter DDL triggers at the database level (not the server level). Thedb_ddladmin fixed database role has this permission implicitly.
ALTER ANY DATABASE EVENT NOTIFICATION	This grants or denies the ability to create and drop database event notifications for service broker. The db_ddladminfixed database role has this permission implicitly.
ALTER ANY DATASPACE	This grants or denies the ability to create a partition schema within the database. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY FULLTEXT CATALOG	This grants or denies the ability to create, alter, or drop fulltext catalogs within the database. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY MESSAGE TYPE	This grants or denies the ability to create, alter, or drop message types for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY REMOTE SERVICE BINDING	This grants or denies the ability to create, alter, or drop remote service bindings for service broker. The db_ddladminfixed database role has this permission implicitly.
ALTER ANY ROLE	This grants or denies the ability to create or drop user-defined database roles. The db_securityadmin fixed database role has this permission implicitly.
ALTER ANY ROUTE	This grants or denies the ability to create, alter, or drop routes for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY SCHEMA	This grants or denies the ability to create, alter, or drop schema within the database. The db_accessadmin,db_ddladmin, and db_securityadmin fixed database roles have this permission implicitly.
ALTER ANY SERVICE	This grants or denies the ability to create or drop services for service broker. The user also must have REFERENCES permissions for all queues and contracts specified for the service. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY SYMMETRIC KEY	This grants or denies the ability to create, drop, or alter symmetric keys for encryption. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY USER	This grants or denies the ability to create, alter, or drop users within the database. The db_accessadmin fixed database role has this permission implicitly.
AUTHENTICATE	Grants or denies the ability to extend impersonation across databases even though explicit access isn't normally permitted.
BACKUP DATABASE	This grants or denies the ability to backup the database. The db_backupoperator fixed database role has this permission implicitly.
BACKUP LOG	This grants or denies the ability to backup the transaction log of the database. The db_backupoperator fixed database role has this permission implicitly.
CHECKPOINT	This grants or denies the ability to issue a CHECKPOINT statement against the database. The db_backupoperatorfixed database role has this permission implicitly.
CONNECT	This grants or denies the ability to enter the database. When a new user is created, it is granted by default.
CONNECT REPLICATION	This grants or denies the ability to connect to the database as a subscriber for the purpose of retrieving a publication via replication.
CONTROL	This grants the equivalent to ownership over the database. The db_owner fixed database role has this permission implicitly.
CREATE AGGREGATE	This grants or denies the ability to create a user-defined aggregate function defined by an assembly. The REFERENCES permission on the assembly must also be possessed.
CREATE ASSEMBLY	This grants or denies the ability to create or drop an assembly within a SQL Server database. If the assembly permission set requires EXTERNAL_ACCESS, the login must also have EXTERNAL ACCESS ASSEMBLY permissions. If the permission set requires UNSAFE, the login must be a member of the sysadmin fixed server role. Unlike ALTER ANY ASSEMBLY, the user must own or have CONTROL permissions on the assembly in order to drop it.
CREATE ASYMMETRIC KEY	This grants or denies the ability to create or drop an asymmetric key. Unlike ALTER ANY ASYMMETRIC KEY, the user must own or have CONTROL permissions on the asymmetric key in order to drop it.
CREATE CERTIFICATE	This grants or denies the ability to create or drop a certificate. Unlike ALTER ANY CERTIFICATE, the user must own or have CONTROL permissions on the certificate in order to drop it.
CREATE CONTRACT	This grants or denies the ability to create a contract for service broker. Unlike ALTER ANY CONTRACT, the user must own or have CONTROL permissions on the contract in order to drop it.
CREATE DATABASE DDL EVENT NOTIFICATION	This grants or denies the ability to create and drop database event notifications for service broker. Unlike ALTER ANY DATABASE DDL EVENT NOTIFICATION, the user must own the database DDL event notification in order to drop it.
CREATE DEFAULT	This grants or denies the ability to create a default. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE FULLTEXT CATALOG	This grants or denies the ability to create and drop fulltext catalogs within the database. Unlike ALTER ANY FULLTEXT CATALOG, the user must own the fulltext catalog in order to drop it.
CREATE FUNCTION	This grants or denies the ability to create a function. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE MESSAGE TYPE	This grants or denies the ability to create a message type for service broker. Unlike ALTER ANY MESSAGE TYPE, the user must own the message type in order to drop it.
CREATE PROCEDURE	This grants or denies the ability to create a stored procedure. This permission is granted implicitly to thedb_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE QUEUE	This grants or denies the ability to create, alter, or drop a queue for service broker. The user must own the queue in order to drop it.
CREATE REMOTE SERVICE BINDING	This grants or denies the ability to create, alter, or drop remote service bindings for service broker. Unlike ALTER ANY REMOTE SERVICE BINDING, the user must own the remote service binding in order to drop it.
CREATE ROLE	This grants or denies the ability to create or drop user-defined database roles. Unlike ALTER ANY ROLE, the user must own or have CONTROL permission over the role to drop it.
CREATE ROUTE	This grants or denies the ability to create, alter, or drop routes for service broker. Unlike ALTER ANY ROUTE, the user must own the route in order to drop it.
CREATE RULE	This grants or denies the ability to create a rule. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE SCHEMA	This grants or denies the ability to create schema in the database. Unlike ALTER ANY SCHEMA, a user with this permission can only drop a schema it owns it or has CONTROL permission over it.
CREATE SERVICE	This grants or denies the ability to create or drop services for service broker. The user also must have REFERENCES permissions for all queues and contracts specified for the service. Unlike ALTER ANY SERVICE, the user must own the service in order to drop it.
CREATE SYMMETRIC KEY	This grants or denies the ability to create or drop a symmetric key. Unlike ALTER ANY SYMMETRIC KEY, the user must own or have CONTROL permissions on the symmetric key in order to drop it.
CREATE SYNONYM	This grants or denies the ability to create a synonym. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE TABLE	This grants or denies the ability to create a table. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE TYPE	This grants or denies the ability to create a type. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE VIEW	This grants or denies the ability to create a view. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE XML SCHEMA COLLECTION	This grants or denies the ability to create an XML schema collection. This permission is granted implicitly to thedb_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
DELETE	This grants or denies the ability to issue the DELETE command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
EXECUTE	This grants or denies the ability to issue the EXECUTE command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
INSERT	This grants or denies the ability to issue the INSERT command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
REFERENCES	This grants or denies the ability to create relationships between objects such as foreign keys on tables referencing other tables or the use of SCHEMABINDING by views and functions. The permission is granted implicitly to thedb_ddladmin fixed database role.
SELECT	This grants or denies the ability to issue the SELECT command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
SHOWPLAN	This grants or denies the ability to see execution plans for queries executing within the database.
SUBSCRIBE QUERY NOTIFICATIONS	This grants or denies the ability to create a subscription to a query notification for when the results of a particular query would change.
TAKE OWNERSHIP	This grants or denies the ability to transfer ownership of an XML schema from one user to another.
UPDATE	This grants or denies the ability to issue the UPDATE command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
VIEW DATABASE STATE	This grants or denies the ability to view conditions about the current database via the database-level dynamic management views or functions.
VIEW DEFINITION	this grants or denies the ability to view the underlying T-SQL or metadata on objects within the database. Thedb_securityadmin database fixed server role has this permission implicitly.

While all of these database-level permissions are important, some of the ones to pay particular attention to are:

• ALTER - can modify the database
• CONTROL - has ownership of the database. Permissions within the database are bypassed for this user
• ALTER ANY USER - can manage users within the database
• BACKUP DATABASE - can create a backup of the database
• BACKUP LOG - can create a backup of the log file.

Listing Permissions

A quick and easy script you can use to see what permissions are assigned at the database level is the following. It uses the sys.database_permissions catalog view along with sys.database_principals to tie to database users and roles:

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
  JOIN [sys].[database_principals] prin
    ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

Granting Permissions

Granting rights is pretty straight forward.  To grant "ALTER" rights to user "DBUser1" you would issue the following command:

GRANT ALTER TO DBUser1