Create Read-Only Database User in SQL Server
- Category: Databases
- Hits: 78014
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.
Cheat Sheet for mySQL vs t-SQL
- Category: Databases
- Hits: 10194
The ReportServer Database
- Category: Databases
- Hits: 7912
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. |
Restore MSSQL Error: Database is in use
- Category: Databases
- Hits: 8814
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.
Modifying columns in a table
- Category: Databases
- Hits: 6144
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;
Reorder Columns in a Table
- Category: Databases
- Hits: 5774
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:
MySQL Oracle equivalents
- Category: Databases
- Hits: 7308
MySQL Group_concat equivalent in T-SQL and Oracle
- Category: Databases
- Hits: 18798
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
Page 1 of 10