• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

High-level description of SQL and its security

I have a 2 page description of Working With Sql Server attached (about 380 words).

Could you please do some sanity check on its content to comment as to how I could improve it.

Note: This is bird-eye view and introductory to SQL Serever prepared for person with little programming skills and interest in the subject.
Mike Eghtebas
Mike Eghtebas
4 Solutions
Vitor MontalvãoMSSQL Senior EngineerCommented:
eghtebas, your security section still not right. The analogy with a restaurant still not good.
The authentication methods are two only (you can see that if the security properties of an instance) and ALL of them needs privileges at instance level and database level, otherwise you wouldn't be able to access data.

I recommend you to start with the definition of a Database Management System, followed by the definitions of Instance, Database, Login, User and Schema. I think when you have these concepts in your mind then will be more easy to write the rest of the document.

Good luck.
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
re:> your security section still not right
I may have many things wrong. What will be helpful is take a sentence or two and write it they way it is right. By just saying it is not right, is not helpful at all.

re:> The analogy with a restaurant still not good.
I intend to keep this analogy but modify it in such a manner that makes sense. You are welcome to help me on that if you can come up with a better way of saying it. Or, make a better analogy.

re:> The authentication methods are two only
Yes, my bad. They are:
1. Windows level.     <-- authentication
2. Sever Level.          <-- authentication
3. Database Level.   <-- what you will name this? permission?  privilege?

What is the a common word (or category) for all three?

re:> definition of a Database Management System?
What about it. What is that you want me focus about this? Do you have a particular point you want me to focus on it?

re:> followed by the definitions of Instance, Database, Login, User and Schema.
The same as above.

re:> when you have these concepts in your mind
I think I have them in my mind hence this write-up. I probably miss some crucial aspects of them but you never give a clue in your post. I just wonder what exactly you mean. It will be helpful to give me something to go by.

I know that is not probably your style, but you are not telling me what is the problem just hinting there is problem.


Vitor MontalvãoMSSQL Senior EngineerCommented:
Let me begin from the end.

I know that is not probably your style, but you are not telling me what is the problem just hinting there is problem.
It's your document and should be you to write it and that's why I'm avoiding to give you the fish. I prefer to show you how to fish.

What is that you want me focus about this? Do you have a particular point you want me to focus on it?
The document is for you or for others? I had the idea that the document should be a startup for someone that want to know more about SQL Server.

1. Windows level.     <-- authentication
 2. Sever Level.          <-- authentication
 3. Database Level.   <-- what you will name this? permission?  privilege?
Ok. Here is some kind of confusion and maybe mine. I was talking about SQL Server authentication only as you can see from the Server Properties:
Or, make a better analogy.
The analogy that an EE gave you in your previous question was good (building access).
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Hi Mike,

I also do not know why you cling to your restaurant example? Normally there is no kind of authentication in a restaurant and other things which do not fit with this analogy I explained in my last answer to you.

If you tell someone about SQL Server authentication you cannot mix up authentication levels of Windows and SQL Server. You say in your document "Typical SQL Server Authentication Modes" - and that's wrong. As Vitor already told you, there are really only two variants to login to a SQL Server, The Windows method and the mixed mode, where you can use SQL Server login additionally to Windows login.

The only difference between these two is that the Windows login don't need to enter login data again - but at some point you need to enter them: when you login to Windows. The Active Directory creates a security ticket (simply spoken) and this is forwarded to SQL Server if you want to login there, so SQL Server asks Active Directory if that is a valid ticket and if yes, it lets you in - BUT ONLY (!!) if your Windows account was inserted at the instance level as a valid login for THIS instance. If you remove any Windows user from the instance level and you do not have an alternate "sa" SQL Server login nobody would be able to login into SQL Server anymore (although I do not believe that SQL Server lets you remove the last account, but only to show you the difference).

So in case of SQL Server login the only difference is that you additionally have a user maintenance on the SQL Server itself which is not managed by Active Directory and not known by Active Directory. If you need the same username on another instance you would need to create a new user with the same name, but they have nothing to do with each other, although they are named the same. In opposite the Windows account is globally known in the Active Directory, you don't create a new user in SQL Server, you only add a known account managed by Active Directory. So if you lock the account in AD, it is locked in all SQL Servers which uses it. If you lock a SQL Server user in SQL Server, it is only locked on this specific instance, not anywhere else.

You ALWAYS need a user on the instance level, otherwise this user can't do anything else on this SQL Server instance. You additionally need a database login assignment to get access to this specific database. To make it even more complex, you can also add a pure database user without login rights which only exists on database level but not on instance level, this can be used i.e. for "WITH EXECUTE AS" stored procedures. But this user would not be able to login to the database as he doesn't exist on instance level and has no login rights - you see, SQL Server authentication levels are complex, try to find a restaurant where you can sit in without going through the entrance...;-)

"SSMS is always is installed on the local computer." - that's of course also wrong. You CAN install SSMS during installation process on your local computer, but you don't need to do that, and all network users of a SQL Server of course do not have SSMS to access it - that's the normal case.

Indeed you would normally have a database developer which creates the tables and views (=saved queries), this one would give access to this views to the external users and then a user can select one of this views and executes it - or create a dynamic SQL string to query such view in the limits the view offers. The user is normally not allowed to create own views or access tables directly - and he also should not be allowed to work with SSMS in most cases. But in a good secured SQL Server he can also install SSMS and get only the views/other resources he is allowed to use.

I think what Vitor said above is that you should try to get a second Windows account in your network and an independent computer from yours and then try to access your SQL Server again with the other Windows account on this computer. You will see that this is not possible. If you try to configure SQL Server in the way that you can access SQL Server with this account from this computer you'll see how that works.


David ToddSenior DBACommented:

Please clarify the intended audience - if no interest in programming, why the detailed explanation of SSMS? If no interest in programming then I wouldn't be letting them loose with SSMS myself.

How will this user interact with SQL? Via an application? Via assistants as this individual is a c-level executive and doesn't touch a computer, but need them to sign off on the budget? I think that you are trying to do too much in too little space. Your paper seems designed towards either someone from a different database flavour (or really old SQL) starting work on a modern version and needs to know where all the tools are etc.

"SSMS is always installed on the local computer."

This is a sweeping generalisation that needs expansion and clarification. Such as: In our shop, we install SSMS on the local workstation as needed, and try to avoid using the one installed on the server; or As our servers use Sever Core installations, the server lacks a gui to support SSMS, and so we need to run SSMS from our workstation.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Who are the users for this writing are:
- Myself to make sure I understand it.  <<< mainly this
- My college kid and friends.
- Number of my ex-project managers (now friends) who could really use some knowledge about SQL Server.
- All above who also need to use some basic T-SQL.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now