Databases

Create Read-Only Database User in SQL Server

: Category: Databases; 09 November 2010; Hits: 83699

The following describes how to setup a database user with read-only access to the AdventureWorks database.

Using SQL Server Management Studio 2008:

Connect to your database server.
Expand Security > Logins.
Right-click on the user who will be set as having read-only access (in this example "adventureworksro").
Select Properties.
Select User Mapping.
Map the login to the database they will have access to.
Tick the boxes for role membership next to public and db_datareader.
Confirm by clicking OK.

You should get something like the following:

: Category: Databases; 31 January 2013; Hits: 15559

Some memory aids for me so that I don't have to keep looking them up:

: Category: Databases; 08 August 2011; Hits: 17286

Amazing, I have just spent all morning on Microsoft websites to determine what number is the TimeDataRetrieval column displaying. Thank you I know it's an INT. There is just a serious lack of documentation as to what this database is and how it populates its data.

View: ExecutionLog

InstanceName	nvarchar(38)	NOT NULL	Name of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER
ReportID	uniqueidentifier	NULL	The ID of the report (looks like a hexadecimal SSID). It's the unique ID of the report but not unique in the table (can be referenced many times).
UserName	nvarchar(260)	NULL	Windows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername)
RequestType	bit	NOT NULL	User or System. Can be 1 or 0. This was zero "0" when I would run a report as a user.
Format	nvarchar(26)	NULL	This is the rendering format. Mostly RPL if viewed in MS Internet Explorer.
Parameters	ntext	NULL	Parameters and the values they were submitted with.
TimeStart	datetime	NOT NULL	Time report started to run.
TimeEnd	datetime	NOT NULL	Time report finished running? Need to check what finished?
TimeDataRetrieval	int	NOT NULL	Milliseconds spent retrieving the data.
TimeProcessing	int	NOT NULL	Milliseconds spent processing the report.
TimeRendering	int	NOT NULL	Milliseconds spent rendering the report.
Source	int	NOT NULL	Source of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History)
Status	nvarchar(32)	NOT NULL	either rsSuccess or an error code; if multiple errors occur, only the first error is recorded
ByteCount	bigint	NOT NULL	Size of rendered reports in bytes.
RowCount	bigint	NOT NULL	Number of rows returned from queries.

: Category: Databases; 08 March 2013; Hits: 14156

What?
This is an article on how to do a restore from backup on a database but when you get the error: "... database is in use".

-- SQL Server 2005
EXEC SP_WHO          // details on who is logged in
GO

-- SQL Server 2008
EXEC SP_WHO2         // even more details
GO

-- Run as database owner to see ALL connected processes as well.

Why?
For every DBA this is a doddle and doesn't warrant its own article but for those of us who merely use SQL Server Management Studio (SSMS) and Microsoft's SQL Server 2008 R2 for development purposes, the once-in-a-blue-moon restore from backup process is quickly forgotten.

How?
You need to set the database to single-user mode.

: Category: Databases; 24 July 2013; Hits: 8601

What?
This is an article to remind me how to modify a column in a database table the old fashioned way (as in stop making me use GUI interfaces so poorly programmed when even I've made better DBMS tools).

All SQL

-- Add a column to an existing table (giving it datatype char(2) and allowing NULL)
ALTER TABLE myTable ADD myColumn CHAR(2) NULL

-- Delete a column
ALTER TABLE myTable DROP COLUMN myColumn 

-- Reorder a column
ALTER TABLE myTable MODIFY COLUMN misplacedColumn AFTER otherColumn;

: Category: Databases; 22 July 2013; Hits: 13144

What?
Just a quick note to myself on how to reorder columns as I was having difficulty using a phpMyAdmin interface to do this.

How?
Taken from the best forum for programming Qs&As: http://stackoverflow.com/questions/4095481/easy-way-to-re-order-columns

Method: phpMyAdmin
So in the phpMyAdmin interface, apparently, you can drag the columns by clicking and holding on the headers when viewing a table structure... Though maybe this is derived from a template and depending on if you are using MS Internet Explorer...

Can't get it working? I use whatever is most useful and Google's Chrome is the fastest browser I have. Here are some ways to do this:

: Category: Databases; 27 September 2010; Hits: 23668

The title of this article implies something rather odd and upcoming considering that Sun Microsystems bought MySQL and Oracle bought Sun. But in fact, this is just a quick list of some regular commands in MySQL that I need in Oracle:

: Category: Databases; 28 November 2011; Hits: 22118

MySQL is just the best
Unfortunately I make a living using Microsoft and Oracle products. I shouldn't say unfortunately as I don't see myself doing any other job and it beats daytime television any day.

I use this quite a lot so I thought I'd put an article here somewhere. Based on the following concept:

RowID     column_to_return_as_string   
--------- --------------------------
1         Me
2         Myself
3         I

-- to be returned as
RowID     my_field_name   
--------- --------------------------
1         Me,Myself,I