Get list of users from a local group

This post is slightly deviating from my routine SQL scripts. :)

Here is the command to get the list of users present in a local group.
This is command shell script and the results can be directly written to a output file.

To get the list of users from local administrator group, run the below command from the command prompt.
net localgroup Administrators >C:\LocalAdministrators.txt

The same can be executed from SQL server using the xp_cmdshell extended stored procedure.

EXEC xp_cmdshell 'net localgroup Administrators'

Moving database objects between schemas

Sometimes database objects gets created under different schema names.
This can be intentional or accidental. In either of the cases, if you decide to move an database object between schema then you can make use of the below script.

To move a database object to between schema

ALTER SCHEMA [Target Schema Name] TRANSFER [SchemaName].[ObjectName]

To move a database object to dbo schema

ALTER SCHEMA [dbo] TRANSFER [SchemaName].[ObjectName]

To move multiple database objects between schema, execute the result set generated by the below query

SELECT 'ALTER SCHEMA [Target Schema Name] TRANSFER ['+SCHEMA_NAME([schema_id])+'].['+[name]+']' 
FROM sys.objects WHERE SCHEMA_NAME([schema_id]) NOT IN ('dbo', 'sys')

The Service Broker in database "<Database Name>" cannot be enabled because there is already an enabled Service Broker with the same ID.

You receive the below error when you try to enable service broker on a database.

Msg 9772, Level 16, State 1, Line 1 The Service Broker in database "<Database Name>" cannot be enabled because there is already an enabled Service Broker with the same ID. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

This usually happens

1. When the service broker is enabled on a database and it is overwritten and then you try to enable the service broker on it again
2. When the service broker is enabled on a database and it is renamed and another database with the same name is created and then you try to enable the service broker on it again.

To fix this error, run the below command which will generate the new ID for the service broker.

ALTER DATABASE <Database Name> SET NEW_BROKER

Different ways to make a table read-only

There may be many cases where a tables needs to be made as read-only in order to maintain the existing data. This can be achieved using many ways and out of which the common methods are

• Deny Permission
• Put the table on read only file group
• Create trigger instead of insert, update, delete
• Make the database read-only
• Create a Column Store Index

When was a Database Deleted and by whom

Here is a T-SQL query which gives information on who and when was a database deleted.

SELECT name AS EventName, DatabaseName, StartTime, LoginName
    FROM sys.traces T CROSS Apply 
 fn_trace_gettable(CASE WHEN CHARINDEX('_', [path]) <> 0
                           THEN SUBSTRING(PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
                           ELSE [path]
                         END, max_files) TT
    JOIN sys.trace_events TE ON TT.EventClass = TE.trace_event_id
WHERE name like '%Deleted%' 
 AND ObjectName IS NULL 
 AND EventSubClass = 1
ORDER BY StartTime DESC