Show information about the oldest open transaction DBCC OPENTRAN(‘tempdb’)
Backup Options WITH NOINIT - Append to files contents if already exists. One file, multiple backups contained. WITH INIT - Overwrite file contents if already exists
A differential backup uses extents - 64K block sizes - 8 pages
A point in time restore only works with the ‘WITH RECOVERY’ option. This is due to it expects a point in time restore to be the last action carried out as you replay the logs. Once executed with ‘WITH RECOVERY’ the database will be brought online.
If you need to backup the tail log after datafile failure, you will need to use the ‘CONTINUE_AFTER_ERROR’ option with the log backup as normally it will fail due to the data file issues.
When starting a SQL Server instance in single user mode, also disabled SQL Server Agent as this will most likely log in first and take the single user connection.
###AM Session - Authenticating, Authorising Users and Roles
There are roles on the server level as well as the database level. Roles on the database level are obviously unique to each database.
Authenication - Who are you
Authorization - What can you access
Principals - Users and logins
Securable - Objects - Like Schema, tables
SQL logins maps to database users. Users are a subset of the logins though both can exist without the other though will not function. There is an exception to this rule, see paragraph below on Database containment.
There is a new feature in SQL Server 2012 called Containment Level which is set on the database level. The default value for the containment level is ‘none’. When the containment level is set to ‘partial’ (In 2012 this is currently the only other option), a number of changes occur to the database with one being that users specified on the database without a SQL login can access the database.
A side affect of using the partial database containment level is that when operating in this mode, connections made to a database in partial containment level may NOT access other databases, even if they are too in partial containment mode and the database user exists there too. Another disadvantage is that a using a partial containment level means the database cannot be used in replication. Also when connecting to a database in partial containment level using SSMS, you must specify the database on log in.
To modify database containment level follow the below in SSMS: Server Instance > Databases > ‘DatabaseName’ > Properties (Right Click DB) > Options > Containment Type
Double Hop Problem - To fix the double hop problem you must configure SPN’s so that the users token is passed along the chain when making the requests. This related to Kerberos. You use SETSPN.exe from the Windows Resource Kit.
Only in SQL 2012, can you configure it to automatically register SPN’s. http://sqlmate.wordpress.com/2012/04/16/managed-service-accounts-with-microsoft-sql-server-2012/
Impersonation - Act as another user on the local machine
Delegation - Act as another user on remote machines
If a user to be use able to impersonate you must grant them the right first. Members of the sysadmin role will be able to impersonate all users without having to specify the right. http://sommarskog.se/grantperm.html
To execute SQL as another user, you need to run the SQL statement below. Once finished then run the REVERT statement.
You can always check what user you are currently running as by running the first statement. Running the second status will show you the true underling login connected.
When restoring databases or troubleshooting logging in issues, you may find that the SID’s for the users do not match. You can check this with the query below. You will also need to fix this by ensuring the SID’s on the login and user object match up. Note using sp_change_users_login is the old method which has now been superseded by ALTER USER.
Login and User management snippets
CREATE LOGIN - http://technet.microsoft.com/en-us/library/ms189751.aspx
Role and Permissions management snippets
In an example in the above code block, you can grant permissions on the column level. It may be best to create a view to hide columns rather than enter the world of column permissions as they may become hard to manage.
In SQL Server 2012 it is now possible to create your own roles.
Table function return a dataset so the user requires the SELECT permission.
Scalar functions return a single value which requires the user to have the execute permission.
When trying to troubleshoot permission issues, you will need to look at the user token which can be used to track which role membership has given them access to the resource in question.
A good link to some SQL for reporting on permissions http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-20053A00-View-all-permissions–_28002_2900.aspx
###PM Session - Auditing</strong>
The old fashion of auditing was using triggers though there were limitations with using triggers. These limitations and side affects included no triggers available for SELECTS, performance impacts and the order in which triggers were fired couldn’t be guaranteed. Also triggers couldn’t be disabled, only deleted.
Example DML trigger
Another method previously used though is being phrased off in future versions of SQL Server is SQL Server Profiler with is GUI tool. The syntax version of this is basically SQL Trace. SQL Server Profiler has been superseded by Extended Events. Extended Events is the general event handling system used by SQL Server. http://technet.microsoft.com/en-us/library/ms181091.aspx http://technet.microsoft.com/en-us/library/bb630354(v=sql.105).aspx
SQL Server Security Auditing was first introduced in SQL Server 2008 and is not turned on by default. To create a new audit use the below instruction. Note, once created, you will need to enable the audit. You can also create the audit on the database level too underneath the database, then security. SSMS > Server > Security > Audits > Right Click - New Audit
Change Data Capture (CDC) - Tracks changes to data and gives a before & after view as the change happens. By default, CDC is not enabled and naturally is unable to track who actually changed the data. If tracking of who did what is needed, you will need to link CDC into something like Extended Events. CDC only tracks changes, so therefore doesn’t track SELECT statements. http://www.mssqltips.com/sqlservertip/1474/using-change-data-capture-cdc-in-sql-server-2008/
Change Tracking (CT) - Tracks changes to rows, though not the data that actually changed. CT is not enabled by default. http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/
CLR Assemblies written in the .NET framework can be loaded into SQL Server and can be accessed from functions, etc. This allows for some application logic to be moved into SQL Server and to also expand it’s functionality.
When creating the assembly in SQL Server, you need to set the Run Type which sets what resources the assembly can access.
Safe - Has no access to external resources. SQL only.
External Access - Can access external resources like file system and network.
Unsafe - Same as external access though can also run unmanaged code. In SSMS, Unsafe is called unrestricted in the UI.
This can have positive performance effects as it doesn’t have to calculate the row counts and also reduces messages sent to client every each query in a proc.