I’m looking to create a database in SQL Server 2012 Express and, although I’ve read several articles by Microsoft and others, I still don’t have a good handle on how to implement a secure database on a LAN with various levels of user permissions. (I have a pretty good understanding of database structure and I’ve had no problem creating a functional unsecured database.)
I want to make sure that users have access to specific Views (based on their security level) and nothing else.
I’d like to start with a sample database and test functionality and security, and I’m looking for an overview of the basic steps. (I have full administrative rights for the various servers.)
Any suggestions? Any resources that are especially useful?
Thanks.
That’s a pretty broad topic. I can probably help with more specific questions like “should I do A or B,” but until you reach that point you’ll probably have the most luck perusing some online guidance documents.
Is there a client application that will be used to connect to the database, or do you plan to give users direct access with something like SQL Management Studio? Have you determined the authentication scheme you will use (Windows vs SQL Server)?
As a starter, you can deny all rights on all your tables to your users. Then, as you create views, give users Select rights to the views. Complications can arise if the tables and views have different owners, though.
Yes Raza, that’s what I wanted to do. I just didn’t know how to do it.
TroutMan, thanks for the links. I’d already seen (and read some of) the first three links, and the other two have some interesting stuff.
It turns out that I was missing one key piece of the puzzle: How to add Windows user groups using SQL Server Management Studio.
It was easy once I figured out that I had to go to Logins in the main Security node, not Users in database Security (and not in Permissions for each Table or View) and to select “Groups” in Object Types when using the Search function.
(I am in no way affiliated with any of them, other than as an occasional poster).
Twitter hashtag for SQL Server related questions: #SQLHELP
There are literally tons and tons of people out there who will offer to help you if you present yourself in a respectful way (ie say please and thank you, not “help me now, bitches!”).