So Ive got my data going to my mssql db but the data (db and/or table) needs to be locked down/ unable to be edited once it is written to the table, but able to be read and queried.
Im wondering if anyone knows of a way to do such a thing in mssql server management studio(2012). There has got to be a way to do it, im just not finding it searching the net.
Thank God for all you that are smarter than me!
You should handle this through two different users, two different connections to the same database. The connection that writes the data to the database should have full access. But every other connection to the database should have read-only privileges. That should probably be your default connection to the database. It’s all handled in MS SQL’s security.
Yep, I agree with Adam, just to get you going in that direction, here’s an article that shows how to create a read-only user: http://www.joellipman.com/articles/microsoft/sql-server/454-create-read-only-database-user-in-sql-server.html
Regards,
I know you specified MSSQL so this is probably out of scope but, I the data is truly historical data that should never be allowed to change, the best answer I’ve ever seen is MySQL’s Archive storage engine.
The next best thing for other databases would be to create triggers that prevent delete and update.
The roles/users and permissions mentioned above is another way that works well if the different users are accessing the db through different applications. (Users should never be accessing the db directly. They should always be coming through an application). If all users are using the same application, and triggers are not an option, then the application has to enforce the permissions.
At the risk of starting a posting war, (what the heck, I need to keep my post count up 
) the principles I use when designing a system is the db should be responsible for maintaining data integrity and the application should be responsible for maintaining the data.
If I read your post correctly, it appears you are asking how to lock down your data in SQL Server itself, with no concern about Ignition at all. I am not the world’s best SQL server admin, but I will show you how I do it.
Unless you change the default settings, the sa (system administrator) login has all rights to everything in the SQL server, so it can always be used to go and change or delete anything it wants. That being said, that account is usually not made accessible to anyone but trusted administrators of the SQL server. So it is unlikely that anyone would use it to mess with your data.
Since all of our SQL servers are on our company domain, what we usually do is have IT create a domain security group for each database on our SQL server, to which we will have added all the domain users who are to have read-only (Select) permissions to that database. Then we add that domain security group to the SQL server’s Security/Logins. We double-click that login, and set that login’s User Mappings by checking the database or databases on that SQL server for which we want that security group’s members to have read-only access. While the database is highlighted, we click the db_datareader role to add that role to that login for that database. This will allow anyone that is in that security group to query any table in that database with a select statement. But an insert or update or delete will fail, as will a drop command for the table itself.
Now if you are not on a domain, you can still create a login that has read-only access by right-clicking on Security/Logins and selecting New Login. You can select SQL Server Authentication, unselect Enforce Password Policy if you like, and enter a password for that user, as shown below. My examples are done in SQL Server 2008, not 2012, but it should be similar.
[attachment=1]Capture.JPG[/attachment]
Then you would set up the mappings and role for that login the same as you would for a domain security group login, and that login would have read-only access to the selected database(s), just as the domain security group did.
Below is a snip that shows the mapping and role settings for the sample ReadOnlyLogin shown above, that gives that login read-only access to a database named Ignition.
[attachment=0]Capture.JPG[/attachment]
Now, if you want to use that login with Ignition, to access the database in read-only mode, you would simply set up a database connection in Ignition that uses that login for the username and password, and point the connection to that database on that SQL server. Then you can write select queries all day long, using that connection, and be assured that they will in no way harm your data in the database. Even if you write an update or insert or delete query, it will not work.
If, for some reason you decide that you want to manipulate the data in one or more tables in that database, you could set up another login the exact same way, except you would select the db_datareader and the db_datawriter also. You would then build another database connection in Ignition using that new login as the username and password, and then you could build insert, update and delete queries all day long by using that connection.
Just remember, you are the Ignition application designer, and you are the one that will be writing the queries for your databases. So you control what Ignition can do to your data. In my opinion, there is no need for a read-only login. I have only one connection to my database, with full permissions to it. The users of my app cannot write their own queries to my database, so I’m not worried about them corrupting my data while using my Ignition app. If I screw up and write a query in Ignition that corrupts my database’s data, well then I deserve the consequences. 
I hope this is what you are looking for. If I misunderstood, let me know, and I’ll try again.