The following script will add users to a database by first creating a logon for the Database Server then add user entries for each of the databases you want to give access to. Also specified are the role members for each database. Add User Using Windows Authentication — Create user windows Authentication CREATE LOGIN [YourDomainName\JohnJacobs] [...]
Archive of entries posted on July 2009
Add User to Role
To add a user to a role, you must use the stored procedure sp_addrolemember. First you must select the database that contains the user and the role. USE YourDatabaseHere GO EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_accessadmin’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_backupoperator’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_ddladmin’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_denydatareader’, ‘JohnJacobs’ EXEC [...]
Add User to Database
There are two methods to add a user in SQL Server. One way is to do so using code (programmatically), the second way is to use the interface. First we will explore the programmatic way, then we will walk through the interface. First of all, there are two different ways users can login to SQL [...]
Create Table
Below is the basic syntax for the create table script: CREATE TABLE dbo.Employees ( EmployeeID int IDENTITY (1,1) NOT NULL CONSTRAINT PK_EmployeeID PRIMARY KEY NONCLUSTERED ,FirstName varchar(50) NULL ,LastName varchar(50) NULL ,SSN varchar(9) NULL CONSTRAINT ssn_unique CHECK (SSN like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘) UNIQUE ,IsTerminated bit NOT NULL DEFAULT 0 ,DateAdded datetime DEFAULT GETDATE() ,Comments varchar(255) SPARSE NULL [...]
Activity Monitor
The Activity Monitor is a new feature in SQL Server 2008 that provides high-level and drill down information giving good insight into the performance of SQL Server allowing DBA’s to quickly identify the source of slowdowns. To launch the activity monitor, right click on the instance name in SSMS and select ‘Activity Monitor’. This launches [...]
The Merge Statement
Examples, best practices, uses, and benefits of the SQL Server 2008 MERGE statement.
Rename Column
In order to rename a column name, you must use sp_rename. The syntax of sp_rename is the following: exec sp_rename ‘tablename.ColumnName’, ‘NewColumnName’, ‘column’ –objecttype Here is an example: CREATE TABLE Employee ( ID int, FName varchar(50) ) GO EXEC sp_rename ‘Employees.FName’, ‘FirstName’, ‘column’ After executing the script above, you will receive the following informational message: [...]
Alter Table Alter Column
The Alter Column statement can modify the data type and the Nullable attribute of a column. The syntax is the same for SQL Server 2005 and SQL Server 2008 except 2008 allows the sparse attribute to be changed. For the example below, we will begin by creating a sample table, then we will modify the [...]
Alter Table Add Column
Adding a column in SQL Server is done using the ALTER TABLE tablename ADD command. When adding columns you can specify all the same settings available when creating a table. In the example below, we will create a small sample table, then add columns using the ALTER TABLE command. Multiple columns can be specificied by [...]
