Creation of a MS SQL test DB and DB security

Opšte stavke :
You can do only three things with any database object, other than actually use it; you can create it, alter it, or drop it to get rid of it.
Unless permissions are explicitly assigned, only members of the following roles can execute these statements :
sysadmin
dbcreator
db_ddladmin
db_owner
Database roles (also referred to as database-level roles) are security roles that exist on a database level, as opposed to the server level. There are two kinds of database roles in SQL Server : fixed roles and flexible roles.
Fixed roles automatically exist in each database. Adding a user to one of these roles will not change that user’s permissions in any other database.
Fixed roles that are available in SQL Server :
db_owner – users in the db_owner role have it all, within a single database.
db_securityadmin – users can modify role permissions and manage permissions.
db_accessadmin – users have the ability to change database access.
db_backupoperator
db_ddladmin
db_datawriter
db_datareader
db_denydatawriter
db_denydatareader
Public – By default, the VIEW ANY DATABASE permission is granted to the public role.
You should never use the SA (system administrator) account for connecting from a web application.
Fixed roles automatically exist in each database. Adding a user to one of these roles will not change that user’s permissions in any other database.

Kako se vide svi korisnici nad nekom bazom (kod mene nema dodatnih, sve je default) :

Link vezan za bezbednost.

1. Kreirati bazu – desni klik na stavku “Databases”/New Database, pa popuniti stavke (ime baze, gde će da budu fajlovi baze i logovi baze, kako će se isti zvati, kako će im biti dozvoljno da radu, itd)
2. Kreirati tabelu (namestiti se na testnu bazu) :
create table spisak (
rednibroj int,
ime text,
prezime text,
sprat integer,
kancelarija integer
);
Ovime se dobija nova (prazna) tabela :

3. Ubaciti podatke u napravljenu tabelu (radi se kao “New Query”) :
insert into spisak (rednibroj, ime, prezime, sprat, kancelarija)
values (‘1’, ‘Vesna’, ‘Petrovic’, ‘1’, ‘110’);
Gde je “spisak” ime tavele, a (rednibroj, ime, prezime, sprat, kancelarija) su nazivi kolona u tabeli.
4. Kako pregledati sadržaj unesen u tabelu :
use veldatest;
select * from spisak;
U prvom redu naglašavamo koju bazu koristimo za upit.

Odličan tutorial za SQL komande (sa primerima).

Posted in WIN | Tagged , , , | Leave a comment

SQL query by using MS SQL SMS (1)

Before writing a query you should point it to a certain DB (By default it is DB master).
There are two ways to do this :
1.
2. Type inside the query (first line) : USE your-DB-name

Posted in WIN | Tagged , | Leave a comment

Initial settings for MS SQL SMS

Go to Tools/Options and set some basic stuff :
*Letter size (I like medium)

*Line numeration (good idea!)

*Auto-correct/finish for commands (so you do not have to type it all) :

Good youtube link.

Adding comments :
*If it is a one line comment : at the row start put two consecutive minus signs : —
*If it is a multi-line comment, at its beginning put a forward slash and a asterisk : /* and at the end reverse the symbol order : */

Posted in WIN | Tagged , | Leave a comment

SQL 2008 installation errors

Unable to start T-SQL Debugging. Could not connect to computer (“*”).
Since my VM is not in a AD, this is solved by starting SQL SMSS as administrator 🙂

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
This is a message I got after some DB restoration.
I start SQL SMSS as Administrator, log as “sa” (DB admin account created during installation of MS SQL 2008 server), and doa “New Query” :
ALTER AUTHORIZATION ON DATABASE::my-DB-name TO sa;
And that apparently solved the problem.
Good link.

Posted in WIN | Tagged , , | Leave a comment

SQL for beginners, SQL 2008 installation on WIN 2012 R2

So my new job 🙂
I should learn SQL.
And since I am a total tabula rasa (for SQL), this is for total beginners 😉

SQL is a language for accessing databases. It can add tables/rows/columns, data, and do queries.
Normalization1 – a single value in a cell, tied to primary key
Normalization2 – separating one table in two, so that data in tables is not duplicated. Those two tables have primary keys that tie them together.
Normalization3 – no redundant data
Normalization4
Why do all this? Because relational DB can not contain duplicated rows.
Relational DB -DB in which data is easily tied together, examples : MS SQL, Oracle, MySQL……
Table in DB MUST have its “primary key”, which is unique in that table.

Youtube link for MS lecture on SQL
Youtube basic SQL queries.

So as not to spoil some already existent DB, I did the following :
1. Installed VM WIN 2012 R2
2. Turned off the firewall (if it is left “on” it gives VERY strange errors)
3. Installed .Net FW 3.5 (dependency for SQL server 2008), this must be done through Server Manager or CMD (as Administrator).
Mount there WIN 2012 R2 instllation (ISO is mounted on F:) :
dism /online /enable-feature /featurename:NetFX3 /all /Source:f:\sources\sxs /LimitAccess

Good link for this.
4. Installed MS SQL Server 2008 SE (test)
5. Check up if it is started after server restart : “server manager”/Tools/Services
6. Do Windows Update, and reset the server (again!). Control Panel/Windows Update
7. Copy some small DB, and do its restore to VM

Posted in WIN | Tagged , , | Leave a comment

Crochet 81 : reddish doily

It turned out soooo cute!
I only need to see what am I going to do with it 🙂

And pattern I used :

Posted in Relaxation | Tagged , | Leave a comment

Crochet 80 : purple triangular schawl

It turned out great 🙂
The edge is done in purely purple.
And here it is :


And pattern I used :

Posted in Relaxation | Tagged , , , | Leave a comment

Skype for Business – gets on my nerves!

It is my PC : WIN 7 with Office 2016.
Well, it installed itself, and did not ask for permission 🙁
And will not let me uninstall it!
It is not under “Programs and Features”.
Since I do not have Office 365 (in which it is automatically included), uninstall is not simple, but can be done.
Go to link, download the msi file, install it, and restart the PC (instructions are from this link).
After this “Skype for Business” will appear under “Programs and features” and you can uninstall it.
And another restart.
And than I put up ordinary Skype 🙂
YESSS!

Posted in WIN | Tagged | Leave a comment

Crochet 80 : ear band

Yeeees, I had some time on my hands, but the end result is quite OK :

And the pattern that I used :

Cvetić je iz tri dela, crveni, pa oranž, i dugmence odozgo 🙂

Posted in Relaxation | Tagged , , | Leave a comment

WIN server defragmentation, how to see the level

It is connected to my previous post.
This is for WIN 2012 server.
Level of (de)fragmentation can be seen in a number of ways :

Way 1 : CLI (link)

Note : this is applicable also to WIN 2003 server.

Way 2 : from Server Manager

Posted in WIN | Tagged , | Leave a comment