Below is a simple script to get the database owner names.
SELECT name AS [Database Name], suser_sname( owner_sid ) AS [Database Owner Name] FROM sys.databases
Cheers!
No comments
Tag: Database Developing
SQL Server – Get All Column Names from a Particular Table
There are two techniques to achieve this: 1) Using Sys Schema; 2) Using INFORMATION_SCHEMA.COLUMNS
Technique 1: Using Sys Schema
SELECT OBJECT_SCHEMA_NAME (c.object_id) SchemaName, o.Name AS Table_Name, c.Name AS Field_Name, t.Name AS Data_Type, t.max_length AS Length_Size, t.precision AS Precision FROM sys.columns c INNER JOIN sys.objects o ON o.object_id = c.object_id LEFT JOIN sys.types t on t.user_type_id = c.user_type_id WHERE o.type = 'U' -- and o.Name = N'TableName' ORDER BY o.Name, c.Name
Technique 2: Using INFORMATION_SCHEMA.COLUMNS
SELECT * FROM SVS.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'TableName'
Cheers!
SQL Server – How to Check if SSL is Enabled
Run the query below to immediately detect if SSL is configured on your SQL Server.
SELECT session_id, encrypt_option FROM sys.dm_exec_connections
This will show the session_ids currently connected to SQL Server. If the value of encrypt_option is TRUE, then it is using a secured connection.
Cheers!
SQL Server – syspolicy_purge_history Job Failed to Run
I just installed an Enterprise Editon of SQL Server 2016 on Windows 2012 R2. I noticed the syspolicy_purge_history is failing.
Below is the error message. I observed the line set-ExecutionPolicy RemoteSigned -Scope process -Force
I tried to set the execution policy to RemoteSigned in Powershell as what was suggested in the error message but was not successful even when I run powershell as Administrator.
What is syspolicy_purge_history?
This job removes policies evaluation history. When a policy runs, it stores the results in MSDB. Over a period of time, this may will cause unnecessary data getting piled up in MSDB. The job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management.
CAUSE:
The ‘Execution Policy’ at the Machine Policy scope is currently set to Unrestricted. This may override the configuration at the ‘Process’ scope.
SOLUTION:
This issue may be resolved by setting the ‘ExecutionPolicy’ to ‘RemoteSigned‘. But I could not change the ‘ExecutionPolicy’ in Powershell. So what I did was to change it directly in the registry.
I verified the change I made in Powershell.
The job syspolicy_purge_history now runs successfully.
SQL Server – VIEW SERVER STATE
VIEW SERVER STATE is a server level permissions that allows a user to view Dynamic Management Objects.
Users will encounter the error below if they don’t have this permission.
Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
There are 2 ways to grant this permission to a user: T-SQL and SSMS
- Via SSMS.
From Server properites to to Permissions.
2. T-SQL
GRANT VIEW SERVER STATE TO AppMIS
Cheers!
SQL Server – A Better Way to Rename a SQL Server Database
I used to rename a database like this.
ALTER DATABASE ConorDB MODIFY NAME = KhabibDB;
But if you use the above method, the logical name and file name will not change. Let’s verify using the script below.
USE master GO SELECT name AS [Logical Name], physical_name AS [DB File Path], type_desc AS [File Type], state_desc AS [State] FROM sys.master_files WHERE database_id = DB_ID(N'KhabibDB') GO
So you will notice that, even though we renamed the database to KhabibDB, the logical name and file name is still ConorDB. Nevertheless, you have accomplished your objective of renaming your database.
But wait! There is a better way of renaming SQL Server databases that I’m going to show you now!
The procedures below will not only rename your database, but it will also rename the Logical Name and the File Name of the database.
A Better Way To Rename a SQL Server Database
1. Place database in a single user mode.
ALTER DATABASE [ConorDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2. Modify the logical names.
ALTER DATABASE [ConorDB] MODIFY FILE (NAME=N'ConorDB_data', NEWNAME=N'KhabibDB_data') GO ALTER DATABASE [ConorDB] MODIFY FILE (NAME=N'ConorDB_log', NEWNAME=N'KhabibDB_log') GO
3. Detach the database so that we can rename the physical data files.
USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'ConorDB' GO
4. After we have detach the database successfully, we can now rename the physical data files. We can do this either manually or by using xp_cmdshell system stored procedure. The xp_cmdshell feature can be enabled using the sp_configure system stored procedure.
USE master GO sp_configure 'show advanced options' GO /* 0 = Disabled , 1 = Enabled */ sp_configure 'xp_cmdshell', 1 GO RECONFIGURE WITH OVERRIDE GO
5. Now that xp_cmdshell is enabled, we can proceed to rename the physical files of the database using the script below.
USE [master] GO EXEC xp_cmdshell 'RENAME "S:\Demo\ConorDB_data.mdf", "KhabibDB_data.mdf"' GO EXEC xp_cmdshell 'RENAME "S:\Demo\ConorDB_log.ldf", "KhabibDB_log.ldf"' GO
6. After that’s done, let’s attach the database using the script below.
USE [master] GO CREATE DATABASE KhabibDB ON ( FILENAME = N'S:\Demo\KhabibDB_data.mdf' ), ( FILENAME = N'S:\Demo\KhabibDB_log.ldf' ) FOR ATTACH GO
7. You may rename the actual database now if you have not done so.
ALTER DATABASE ConorDB MODIFY NAME = KhabibDB;
8. Lastly, let’s allow multi-user access for the database.
ALTER DATABASE KhabibDB SET MULTI_USER GO
9. Use the script below the verify the Logical and Physical File Names of the database.
USE master GO SELECT name AS [Logical Name], physical_name AS [DB File Path], type_desc AS [File Type], state_desc AS [State] FROM sys.master_files WHERE database_id = DB_ID(N'KhabibDB') GO
SQL Server – Installing SQL Server 2016 Developer Edition (Free) with Screenshots
NOTE: SQL Server Management Studio is not part of the SQL Server 2016 media anymore Please download it here
1. Download SQL Server 2016 Developer Edition. I downloaded SQL Server 2016 Developer Edition using my MSDN account.
Alternatively you may go this link to get the SQL Server 2016 Evaluation.
2. Right-click then Run SQL Server 2016 setup as administrator.
3. Choose Installation on the left-pane then click New SQL Server stand-alone installation… (the first choice).
4. I’m installing a Developer edition (free) hence there is no need to enter a product key. Click Next.
5. Accept the license terms then click Next.
6. I opted not to tick User Microsoft Updates. Click Next.
7. Ignore the warning about Windows Firewall. This means that the Windows Firewall for Database Engine Access is not yet configured. We can do that later. Click Next.
8. Select the necessary features that you need. You can read the descrption in the feature description box.
9. Choose Default instance if you are not going to create multiple instances of SQL Server on the same machine. Otherwise go for Named Instance. Click Next.
10. Enter the service accounts that you will use to run SQL Services. I’ve set the Startup Type to Automatic for all components.
11. Go to the Collation tab. I’ve accepted the defaults because this is exactly what our application needs. Some application require you to choose a specific collation. You can click Customize to change it. Click Next.
12. I’ve chosen Mixed Mode. This is highly recommended so that there is an additional built in SA accout with a separate user name and password. To add users such as the one I’m using, click Add Current User. to allow account to log into SQL Server.
13. I left the Data Directories to defaults. Though it can be changed if you have a multiple disk environment. Additionally, for better performance, you may want to separate out where different parts of the DBMS go.
14. Configure multiple tempDB data files. This is to increase the I/O throughput to tempdb. When you create multiple data files, they will all be in the primary filegroup and SQL Server uses a proportional fill algorithm to determine which file to use for each request to create an object. If all the files are exactly the same size, then SQL Server uses the files in a “round robin” fashion, spreading the load equally across the files. As a general guideline, create one data file for each CPU on the server and then adjust the number of files up or down as necessary.
15. I did not enable FILESTREAM but you may want to if you plan to explore large file types such as BLOB. Click Next.
16. If you have selected Analysis Services component, you will be shown with the following screen. Select the Multidimensional and Data Mining Mode. Click Add Current User. Click Next.
17. Leave the Data Directories to default. Click Next.
18. If you have selected Reporting Services component, you will be presented with the following screen. I chose Install only. We will configure it later. Click Next.
19. Review selected features and click Install.
20. Once completed. Click Close.
SQL Server – Troubleshoot a Transaction Log Full Transaction Error (Msg 9002)
I encountered the error below.
Msg 9002, Level 17, State 2, Line 1
The transaction log for database ‘DBName’ is full due to ‘LOG_BACKUP’.
The following are the corrective actions that can be done.
- Take a log backup (The #1 cause of full transaction log is the lack of backups)
- Kill a long-running transaction
- Alter database mirroring, availability groups, or replication to allow the transaction log to clear
- Either manually grow the existing log file(s) or add another log file (temporarily)
- As a last resort, switch to SIMPLE recovery model
Execute the script below to know what is the reason why the log cannot be cleared.
SELECT [log_reuse_wait_desc] FROM [master].[sys].[databases] WHERE [name] = N'DB_Name'; GO
Some examples of why the log is not clearing are LOG_BACKUP, DATABASE_MIRRORING, NOTHING.
You may check the full list in Books Online at http://bit.ly/PoX2xe
SQL Server – View Orphaned Users
Orphaned users in SQL Server occur when a database user is based on a login in the master database, but the login no longer exists in master. This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. This topic describes how to find orphaned users, and remap them to logins.
Below is a script that I use to view orphaned users.
DECLARE cur CURSOR FAST_FORWARD FOR SELECT name FROM sys.databases WHERE database_id > 4 OPEN cur DECLARE @SQL NVARCHAR(MAX), @DBName SYSNAME DECLARE @Results TABLE (DBName SYSNAME, UserName SYSNAME, UserSID VARBINARY(MAX)) FETCH NEXT FROM cur into @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'USE ' + @DBName + ';SELECT ''' + @DBName + ''' AS DBName, UserName = name, UserSID = sid from sysusers WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND (LEN(sid) <= 16) AND SUSER_SNAME(sid) IS NULL' INSERT INTO @Results EXEC(@SQL) FETCH NEXT FROM cur into @DBName END CLOSE cur DEALLOCATE cur SELECT * FROM @Results
SQL Server – MAXIMUMERRORCOUNTREACHED Error (Maintenance Plan)
I setup a maintenance plan to backup my databases. I ran it and I hit the error below.
The problem is because there are a couple of databases that are having an error -transaction log full due to CHECKPOINT (This is a different story). Hence, I’m not able to backup these databases. If you look at the log closely, you will notice the errors (refer to the screenshot below).
So what I did was to deselect these databases to make the job run successfully.
Alternatively though, you increase the maximum error count to make the job go thru successfully despite hitting errors.
- Right-click on the Backup Task then go to Properties.
2. Increase the number of the “MaximumErrorCount” parameter.
Cheers!