Microsoft SQL Server
Map users to logins
“Why can’t I login after the database restore?”
As a DBA, this is one of the questions I get most.
Microsoft SQL Server
Deny updates on a table but allow inserts
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.
Microsoft SQL Server
List SQL server roles by users
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.
Microsoft SQL Server
A replacement for syslogins
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.
Changing the database owner
When a database is created, the logged in user is set as the database owner as default. Often, this user account is a physical person and he or she may get more permissions in the database than intended. Normally, a system account should be used as database owner. For instance sa.
Note that when you change the database owner the old account may get less permissions than before, not being the owner (who can do anything) anymore. So check how and where the old account is used before changing.