Sometimes it is nessesary to deny updates on a table, but allow inserts. This is the case when the history must never be changed, for instance in a table with accounting data.
This can be done in two different ways. The first method is to use permissions on the table and make sure everyone is denied update permissions on the table. The other method is to use an “instead of update” trigger.
Here I’ll show both methods.
When designing workflows in Automate BPA Server, I like to “draw the big picture” first. That is, lay out the different major steps in the workflow and adding details and logic to them later.
Problem is, you can’t just add an embedded workflow to an existing workflow without getting errors.
SQL Server roles can be used to give users server wide privileges. The server roles are sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator and bulkadmin. The mapping between users and server roles can be found in the system view sys.server_role_members.
Here I’ll show a SQL script that lists all users and their server roles.
The system table sys.syslogins was marked as deprecated in SQL Server 2005, and is included up until 2012 for backwards compability only.
So what is the replacement of sys.syslogins? As it turns out, there is no exact match in SQL Server 2005 and later. Instead there are various system views that can be combined to gather the same information.
I created a SQL statement to combine those system views into a single view, matching the old sys.syslogins.
If you are using SQL Server log shipping (or mirroring), you probably want to copy the logins from the primary server to the secondary.
When databases are log shipped, the users go along with their respective databases. But those users are connected to a login on the primary server. Those logins are not copied automatically to the secondary. Users without connection to a login are called orphaned users.
This has the effect that users may not be able to login on the secondary in case of a failover.
One solutions to this problem is to transfer logins manually. That may be the best solution if there are very few logins to manage. But if there are more than just a few logins, an automatic solution is required.
There are many methods available to handle this. In this blog post I’ll combine a couple of the best solutions available, and add a few features that I found missing.
If your database contains very large tables, querying or managing those tables may fill tempdb. When tempdb is full, an error is raised and the transaction is rolled back. If this will happen or not depends on how large your tempdb can get. Even if the size of tempdb isn’t limited by turning off autogrowth or by setting a max size, the disk space available is the upper limit for how big tempdb can get. Check out this blog post to see how to calculate the maximum possible size of tempdb.
Large tables can be a latent problem, since there may be no problems at all until someone, for instance, tries to sort the table in a query. SQL Server uses tempdb (as the name implies) as a temporary workspace for data, and if SQL Server can’t handle all the data in memory tempdb is used.
To avoid this problem you need to know if there are very large tables that won’t fit in tempdb when at its maximum size. Here is a script to look for tables larger than tempdb:
There is always a limit on how large tempdb can get. Even if there are no size limitations on the data files in tempdb, the limit is still the free space on disk drives. Here I’ll show how to calculate the maximum size of tempdb (data only, not log).