CogKnowHow

Product:

Microsoft SQL server 2016

Microsoft Windows 2012 server

Issue:

How add read access to all tables in all databases on one server to a user?

Solution:

Add a AD user as a login to the SQL server in SSMS. https://www.guru99.com/sql-server-create-user.html

In our example we use User3, but you change it to your login-name.

EXEC sp_MSforeachdb ' Use ?; Create user User3 for login User3; Grant select on database :: ? to User3 '

In the string after the sp_MSforeachdb  command, the character ? is replaced by each database.

Use ?, this will move focus to this database, and execute the command after to this database.

Create user User3 for login User3, adds the user name to the database and connect it with the login user.

If the user3 already is in the database, then above will not work.  Then run the command again without the Create user… part.

Grant select on database :: ? to User3, will give this user READ access to all object in the database. Even new tables and views.

The grant select on a database, will give that user full read access to the database tables and views.

More information:

https://www.mssqltips.com/sqlservertip/6702/sql-server-windows-authentication-with-users-and-groups/

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.techonthenet.com/sql_server/users/create_user.php

https://www.tutorialsteacher.com/sqlserver/grant-permissions-to-user

https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.mssqltips.com/sqlservertip/1476/how-to-read-log-file-in-sql-server-using-tsql/

https://dba.stackexchange.com/questions/117759/granting-select-access-to-all-tables-within-a-specific-database-in-ms-sql

To grant SELECT permissions on the whole database use this:

USE <MY_DATABASE>
GRANT SELECT ON DATABASE :: <MY_DATABASE> TO <MY_USER>

where

• <MY_USER> is user
• <MY_DATABASE> is database name

Granting permissions on schema doesn’t help, simple reason: if new schemas occur the user will not have permissions.

Granting permissions on all objects doesn’t for the same reason, the user will not have permissions on new objects created after GRANT event.

Product:
Microsoft SQL Server 2016

Microsoft Windows 2012

Issue:
How read the SQL agent jobs logs without being a sysadmin?

Solution:

Add the user to a AD group and add the AD group as a login in the SQL server. In our example we will instead use SQL login User3.

In SSMS go to Security – Logins – New Login.  https://www.guru99.com/sql-server-create-user.html  Create your login in SQL.

Go to your MSDB database under – Databases – System Databases – msdb – Security – Users – New User.
Add your User3 there to the msdb database.

Expand Membership and select SQLAgentReaderRole, then click OK.

Then that user can in SSMS see SQL Server Agent, and View History and properties of a SQL Agent Job.

With SQL script:

USE [msdb]
exec sp_addrolemember 'SQLAgentReaderRole', 'DomainName\GroupName'

 

https://zarez.net/?p=36

More Information:

https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?redirectedfrom=MSDN&view=sql-server-ver15

https://www.techonthenet.com/sql_server/users/create_login.php

https://www.tutorialsteacher.com/sqlserver/grant-permissions-to-user

https://www.vb-net.com/Sql/Index.htm

https://www.tutorialsteacher.com/sqlserver/indexes

Product:

Microsoft Windows

git version 2.33.1.windows.1

Issue:
What is a good order of command to work with GIT?

Suggestion:

Install GIT on your computer from here https://git-scm.com/download/win

https://git-scm.com/book/en/v2/Getting-Started-Installing-Git

Create a folder, where you will work with git, in our example we use C:\kurser\git-demo-example

To setup a repository, you can follow this steps: https://towardsdatascience.com/an-easy-beginners-guide-to-git-2d5a99682a4c

When you are inside your git folder, you can start using GIT from tools like GIT BASH or SOURCETREE. https://www.sourcetreeapp.com/enterprise          https://gitforwindows.org/

Git commands are case sensitive, and git commands is always lowercase. git used to that.

If you work with Azure DevOps as master source, the git function can depend on how Azure DevOps is setup.

We guess you have already clone your repo, so you can start to work.

git status  (to check you are in correct folder and branch, mostly main at start)

git pull  (to download the latest changes to the project from the remote master)

git checkout -b  “Feature/1234”  (to create a branch and start working on the files)

Work with your file in Notepad++ or Visual Studio, and save your work in the folder you have for git.

git add .  (to add all changed files to the git source control)

git commit -m “This is a updated file”   (to save the changes to your branch, add a online comment about the files)

git checkout master (switch to your local main branch to update it with any new remote changes)

git pull  (download the latest changes done by others to your main branch)

git checkout “Feature/1234”  (go back to your local branch, you been working in)

git merge master (get the files from master into your branch, any conflicts have to be solved in Visual Studio locally)

You may have to change the word master to main in your version of GIT, also remember that the branch-name is case sensitive.

git push origin “Feature/1234”   (to push your changes up to the remote git server)

Then you have to go to your remote git (or Azure DevOps) in your web browser and complete the pull there.

This picture will look different for what kind of remote repo you use. Above is from https://github.com

Click on Compare & pull request button

Enter some descriptive text and click on Create pull request button.

As we are lucky, no conflicts, you can press Merge pull request. Good practice is to leave explaining comments about what the code change is about.

Click on Confirm merge button.

Recommendation is to Delete the branch, so it is not laying around.

Then you are good to go – and start over from top with your next change.

git log –oneline   (enter to see the steps you have done)

Download and install WinMerge, to have a tool to compare files. https://winmerge.org/downloads/

Download and use Visual Studio Code https://code.visualstudio.com/download to solve conflicts.

git reflog  (to see more detail’s of what you done)

If I accidental delete files in my local folder for the git project, i can reset my state to the one before with command:

git reset  –hard origin

This will only work if i have not committed and push the change up to remote repo.

 

More Information:

https://education.github.com/git-cheat-sheet-education.pdf

https://towardsdatascience.com/an-easy-beginners-guide-to-git-2d5a99682a4c

https://rogerdudler.github.io/git-guide/

https://rogerdudler.github.io/git-guide/files/git_cheat_sheet.pdf

https://www.atlassian.com/git/tutorials/git-bash

https://docs.microsoft.com/en-us/azure/devops/repos/git/pull-requests?view=azure-devops&tabs=browser

https://www.stanleyulili.com/git/how-to-install-git-bash-on-windows/

https://www.freecodecamp.org/news/practical-git-and-git-workflows/

https://docs.microsoft.com/en-us/visualstudio/subscriptions/download-software

https://visualstudio.microsoft.com/vs/older-downloads/

https://visualstudio.microsoft.com/free-developer-offers/

https://github.com/microsoft/vscode

https://medium.com/@deprov447/a-simple-git-guide-8854d5c3ae12

https://git-scm.com/docs/gittutorial

https://www.edureka.co/blog/git-commands-with-example/