Sunday, November 4, 2012

Server and Database Roles in SQL Server

Fixed server roles:

There are 8 types of fixed server roles in sql server

We can get a list of the fixed server roles from exec sp_helpsrvrole

Sys admin            -           can do anything within sql server. Most powerful role in sql server. Limit its access to only those who absolutely need it.

Security admin     -          can manage lo-gins and create database permissions, also read error logs and change passwords.

Setup admin         -           can manage linked servers and start up procedures.

Server admin        -           can set server wide configuration options, shut down the server

Bulk admin          -           can perform bulk operations (i.e., bulk insert statements)

Db creator            -           can create, alter, drop, backup and restore any database

Disk admin           -           members belonging to the disk-admin fixed server role can manage the disk files on the server user i.e., user having disk-admin fixed server roles can delete a backup file present on the server.

Process admin      -          members of the process-admin fixed server role can end processes that are running in an instance of sql serve
Posted by at No comments:

How to Find Version of SQL Server

Method 1
The first is by using either Enterprise Manager or SQL Server Management Studio and right clicking on the instance name and selecting properties
SQL Server 2005 version information
Method 2
Connect to the instance of SQL Server, and then run the following query:
SELECT @@VERSION
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009
10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express
Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Method 3
SQL Server Management Studio (SSMS)
To determine the version of the client tools that are installed on your system, start Management Studio, and then click About on the Help menu.
Method 4
Open Windows Explorer and navigate to the folder where SQL Server is installed such as "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn". 

Find file sqlservr.exe and right click and select properties and a window will open like the one below.  Here we can see that this file is version 10.0.2573.0.

 SQL Server version from file

Method 5

Connect to the instance of SQL Server, and then run the following query:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Note This query works with any instance of SQL Server 2000 or of a later version.

The following results are returned:

· The product version (for example, 10.0.1600.22)
· The product level (for example, RTM)
· The edition (for example, Enterprise)

Method 6

Exec xp_msver

Method 7

Look at the first few lines of the Errorlog file for that instance. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. The entries may resemble the following:

2011-03-27 22:31:33.50 Server      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) 

Posted by at No comments:

SQL Server Version List

SQL Server 2012
11.0.2316 SQL Server 2012 CU1  12-Apr-12
11.0.2100.6 SQL Server 2012 RTM  7-Mar-12



SQL Server 2008 R2
10.50.2811 SQL Server 2008 R2 SP1 CU6  16-Apr-12
10.50.2806 SQL Server 2008 R2 SP1 CU5  22-Feb-12
10.50.2796 SQL Server 2008 R2 SP1 CU4  9-Jan-12
10.50.2789.0 SQL Server 2008 R2 SP1 CU3  17-Oct-11
10.50.2772.0 SQL Server 2008 R2 SP1 CU2  16-Aug-11
10.50.2769.0 SQL Server 2008 R2 SP1 CU1  16-Sep-11
10.50.2500.0 SQL Server 2008 R2 SP1  11-Jul-11
10.50.1815 SQL Server 2008 R2 CU13  17-Apr-12
10.50.1810 SQL Server 2008 R2 CU12  21-Feb-12
10.50.1809 SQL Server 2008 R2 CU11  9-Jan-12
10.50.1807.0 SQL Server 2008 R2 CU10  19-Oct-11
10.50.1804.0 SQL Server 2008 R2 CU9  23-Aug-11
10.50.1797.0 SQL Server 2008 R2 CU8  16-Sep-11
10.50.1777.0 SQL Server 2008 R2 CU7  16-Jun-11
10.50.1765.0 SQL Server 2008 R2 CU6  21-Feb-11
10.50.1753.0 SQL Server 2008 R2 CU5  20-Dec-10
10.50.1746.0 SQL Server 2008 R2 CU4  18-Oct-10
10.50.1734.0 SQL Server 2008 R2 CU3  17-Aug-10
10.50.1720.0 SQL Server 2008 R2 CU2  25-Jun-10
10.50.1702.0 SQL Server 2008 R2 CU1  18-May-10
10.50.1600.1 SQL Server 2008 R2 RTM  12-Apr-10



SQL Server 2008
10.00.5775 SQL Server 2008 SP3 CU4  20-Mar-12
10.00.5770 SQL Server 2008 SP3 CU3  16-Jan-12
10.00.5768 SQL Server 2008 SP3 CU2  22-Nov-11
10.00.5766 SQL Server 2008 SP3 CU1  18-Oct-11
10.00.5500 SQL Server 2008 SP3  6-Oct-11
10.00.4330 SQL Server 2008 SP2 CU9  19-Mar-12
10.00.4326 SQL Server 2008 SP2 CU8  30-Jan-12
10.00.4323 SQL Server 2008 SP2 CU7  21-Nov-11
10.00.4321 SQL Server 2008 SP2 CU6  20-Sep-11
10.00.4316 SQL Server 2008 SP2 CU5  18-Jul-11
10.00.4285 SQL Server 2008 SP2 CU4  16-May-11
10.00.4279 SQL Server 2008 SP2 CU3  21-Mar-11
10.00.4272 SQL Server 2008 SP2 CU2  17-Jan-11
10.00.4266 SQL Server 2008 SP2 CU1  15-Nov-10
10.00.4000 SQL Server 2008 SP2  29-Sep-10
10.00.2850 SQL Server 2008 SP1 CU16  19-Sep-11
10.00.2847 SQL Server 2008 SP1 CU15  18-Jul-11
10.00.2816 SQL Server 2008 SP1 CU13  22-Mar-11
10.00.2812 SQL Server 2008 SP1 CU14  16-May-11
10.00.2808 SQL Server 2008 SP1 CU12  17-Jan-11
10.00.2804 SQL Server 2008 SP1 CU11  15-Nov-10
10.00.2799 SQL Server 2008 SP1 CU10  21-Sep-10
10.00.2789 SQL Server 2008 SP1 CU9  19-Jul-10
10.00.2775 SQL Server 2008 SP1 CU8  17-May-10
10.00.2766 SQL Server 2008 SP1 CU7  15-Mar-10
10.00.2757 SQL Server 2008 SP1 CU6  18-Jan-10
10.00.2746 SQL Server 2008 SP1 CU5  24-Nov-09
10.00.2734 SQL Server 2008 SP1 CU4  22-Sep-09
10.00.2723 SQL Server 2008 SP1 CU3  21-Jul-09
10.00.2714 SQL Server 2008 SP1 CU2  18-May-09
10.00.2710 SQL Server 2008 SP1 CU1  16-Apr-09
10.00.2531 SQL Server 2008 SP1  7-Apr-09
10.00.1835 SQL Server 2008 RTM CU10  15-Mar-10
10.00.1828 SQL Server 2008 RTM CU9  18-Jan-09
10.00.1823 SQL Server 2008 RTM CU8  16-Nov-09
10.00.1818 SQL Server 2008 RTM CU7  21-Sep-09
10.00.1812 SQL Server 2008 RTM CU6  21-Jul-09
10.00.1806 SQL Server 2008 RTM CU5  18-May-09
10.00.1798 SQL Server 2008 RTM CU4  17-Mar-09
10.00.1787 SQL Server 2008 RTM CU3  19-Jan-09
10.00.1779 SQL Server 2008 RTM CU2  17-Nov-08
10.00.1763 SQL Server 2008 RTM CU1  22-Sep-08
10.00.1600 SQL Server 2008 RTM  6-Aug-08



SQL Server 2005
9.00.5266 SQL Server 2005 SP4 CU3  21-Mar-11
9.00.5259 SQL Server 2005 SP4 CU2  22-Feb-11
9.00.5254 SQL Server 2005 SP4 CU1  20-Dec-10
9.00.5000 SQL Server 2005 SP4  17-Dec-10
9.00.4325 SQL Server 2005 SP3 CU15  21-Mar-11
9.00.4317 SQL Server 2005 SP3 CU14  21-Feb-11
9.00.4315 SQL Server 2005 SP3 CU13  20-Dec-10
9.00.4311 SQL Server 2005 SP3 CU12  18-Oct-10
9.00.4309 SQL Server 2005 SP3 CU11  17-Aug-10
9.00.4305 SQL Server 2005 SP3 CU10  23-Jun-10
9.00.4294 SQL Server 2005 SP3 CU9  19-Apr-10
9.00.4285 SQL Server 2005 SP3 CU8  16-Feb-10
9.00.4273 SQL Server 2005 SP3 CU7  21-Dec-09
9.00.4266 SQL Server 2005 SP3 CU6  19-Oct-09
9.00.4230 SQL Server 2005 SP3 CU5  17-Aug-09
9.00.4226 SQL Server 2005 SP3 CU4  16-Jun-09
9.00.4220 SQL Server 2005 SP3 CU3  21-Apr-09
9.00.4211 SQL Server 2005 SP3 CU2  17-Feb-09
9.00.4207 SQL Server 2005 SP3 CU1  20-Dec-08
9.00.4053 SQL Server 2005 SP3 GDR (Security Update)  13-Oct-09
9.00.4035 SQL Server 2005 SP3  16-Dec-08
9.00.3356 SQL Server 2005 SP2 CU17  21-Dec-09
9.00.3355 SQL Server 2005 SP2 CU16  19-Oct-09
9.00.3330 SQL Server 2005 SP2 CU15  18-Aug-09
9.00.3328 SQL Server 2005 SP2 CU14  16-Jun-09
9.00.3325 SQL Server 2005 SP2 CU13  21-Apr-09
9.00.3315 SQL Server 2005 SP2 CU12  17-Feb-09
9.00.3310 SQL Server 2005 SP2 Security Update  10-Feb-09
9.00.3301 SQL Server 2005 SP2 CU11 15-Dec-08
9.00.3294 SQL Server 2005 SP2 CU10  20-Oct-08
9.00.3282 SQL Server 2005 SP2 CU9  18-Aug-08
9.00.3257 SQL Server 2005 SP2 CU8  16-Jun-08
9.00.3239 SQL Server 2005 SP2 CU7  14-Apr-08
9.00.3233 SQL Server 2005 QFE Security Hotfix 8-Jul-08
9.00.3228 SQL Server 2005 SP2 CU6  18-Feb-08
9.00.3215 SQL Server 2005 SP2 CU5  17-Dec-07
9.00.3200 SQL Server 2005 SP2 CU4  15-Oct-07
9.00.3186 SQL Server 2005 SP2 CU3  20-Aug-07
9.00.3175 SQL Server 2005 SP2 CU2  28-Jun-07
9.00.3161 SQL Server 2005 SP2 CU1 15-Apr-07
9.00.3152 SQL Server 2005 SP2 Cumulative Hotfix  7-Mar-07
9.00.3077 SQL Server 2005 Security Update  10-Feb-09
9.00.3054 SQL Server 2005 KB934458  5-Apr-07
9.00.3042.01 SQL Server 2005 "SP2a"  5-Mar-07
9.00.3042 SQL Server 2005 SP2 1-Feb-07
9.00.2047 SQL Server 2005 SP1 
9.00.1399 SQL Server 2005 RTM  1-Nov-05



SQL Server 2000
8.00.2039 SQL Server 2000 SP4
8.00.0760 SQL Server 2000 SP3
8.00.0534 SQL Server 2000 SP2
8.00.0384 SQL Server 2000 SP1
8.00.0194 SQL Server 2000 RTM



SQL Server 7
7.00.1063 SQL Server 7 SP4
7.00.0961 SQL Server 7 SP3 15-Dec-00
7.00.0842 SQL Server 7 SP2 20-Mar-00
7.00.0699 SQL Server 7 SP1 15-Jul-99
7.00.0623 SQL Server 7 RTM



SQL Server 6.5
6.50.416 SQL Server 6.5 SP5a
6.50.415 SQL Server 6.5 SP5
6.50.281 SQL Server 6.5 SP4
6.50.258 SQL Server 6.5 SP3
6.50.240 SQL Server 6.5 SP2
6.50.213 SQL Server 6.5 SP1
6.50.201 SQL Server 6.5 RTM
Posted by at No comments:
Subscribe to: Posts (Atom)