Move User Databases

USE master; GO -- Return the logical file name. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2012') AND type_desc = N'LOG'; GO ALTER DATABASE AdventureWorks2012 SET OFFLINE; GO -- Physically move the file to a new location. -- In the following statement, modify the path specified in FILENAME to -- the new location of the file on your server. ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_Log, FILENAME = 'C:\NewLoc\AdventureWorks2012_Log.ldf'); GO ALTER DATABASE AdventureWorks2012 SET ONLINE; GO --Verify the new location. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2012') AND type_desc = N'LOG';

List of all user databases in SQL Server

SELECT *
FROM   master.sys.databases
WHERE  Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0

Grant/Revoke Permissions to run SQL Server Profiler for a non System Admin User

Security and Permissions

Tracing can expose a lot of information about not only the state of the server, but also the data sent to and returned from the database engine by users. The ability to monitor individual queries down to the batch or even query plan level is at once both powerful and worrisome; even exposure of stored procedure input arguments can give an attacker a lot of information about the data in your database.
In order to protect SQL Trace from users that should not be able to view the data it exposes, previous versions of SQL Server allowed only administrative users (members of the sysadmin fixed server role) access to start traces. That restriction proved a bit too inflexible for many development teams, and as a result it has been loosened.



GRANT/REVOKE ALTER TRACE permission using T-SQL

 We can also assign this permission by running T-SQL commands. We can get this privilege by running the below commands in the Master database. If you want to assign this permission by running this T-SQL command then do not follow Step 3 thru Step 5 above.

--  Grant access to Windows Login
USE Master;
GO
GRANT ALTER TRACE TO [DomainName\WindowsLogin]
GO

-- To Grant access to a SQL Login
USE master;
GO
GRANT ALTER TRACE TO Geoff;
GO

GRANT permission on ALTER trace by T-SQL
Now you can go ahead and launch SQL Server Profiler to verify that you have access.

  If you want to revoke this access from the assigned login then you can run the below commands to remove the ALTER TRACE permission.

--REVOKE access FROM a SQL Login
USE Master;
GO
REVOKE ALTER TRACE FROM Geoff;
GO

-- REVOKE access FROM a Windows Login
USE master;
GO
REVOKE ALTER TRACE FROM [DomainNAME\WindowsLogin]
GO

Connect to SQL Server with Windows Authentication from others Windows desktop

If you want to use your windows credentials to connect to server from your colleagues desktop in process of fixing/debugging a issue.

Try this Steps

• Press SHIFT button 
• Right CLICK on the SQL Server Management Studio button
• Select "Run as different user" and give your windows credential to connect to the server.

SQL Server Endpoint

Configuring endpoint security

A SQL Server endpoint is a door opened from or to SQL Server. Everything that can go from your SQL Server to the network, or from the network to SQL Server, goes through an endpoint. Endpoints can be system or user-defined. The system endpoints allow the usual T-SQL connections to SQL Server to send queries.
The endpoints are defined for a specific protocol, which were either HTTP or TCP in the
previous versions. In SQL Server 2012, the HTTP endpoints previously used for the native
web services feature are removed. Now you only have TCP endpoints.
You can create a user-defined endpoint for three purposes: TCP requests, service broker, or
database mirroring. Here we will talk about the first one, which is useful to set up a dedicated
and secured connection to SQL Server for administrative purposes or distant access.

How to do it...

To configure endpoint security, follow these steps:
1. In a SQL query window, type the following T-SQL command:
CREATE ENDPOINT myTSQLEndpoint
STATE = started
AS TCP (
LISTENER_PORT = 8080,
LISTENER_IP = (127.0.0.1)
)
FOR TSQL ();
2. You will receive a message saying that it will cause the revocation of any Public
connect permissions on the TSQL default TCP endpoint. This effectively means that
all logins will lose their permission to connect to SQL Server through the default
T-SQL endpoint. If you still want to allow connection permissions to the default TCP
endpoint, issue the following command:
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];
3. You can view the state of the endpoints with this command:
SELECT * FROM sys.tcp_endpoints;
Chapter 1
45
4. You can start or stop the endpoints with the ALTER ENDPOINT command.
For example, you can stop the default TCP endpoint as follows:
ALTER ENDPOINT [TSQL Default TCP]
STATE = STOPPED;
The state we just creat@ed will remain even after a service restart.

How it works...

When SQL Server is installed, a system endpoint is created for each network protocol used
in SQL Server. The permission to access these endpoints is given to the Public server role.
Every login declared in SQL Server is a member of this role, and permissions on the Public
server role can be changed, unlike other fixed server roles. You can grant, revoke, or deny
permissions to connect to an endpoint to all the logins through the Public role, or to specific
logins by revoking CONNECT permissions to the Public role, and by granting specific privileges
as follows:
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [a_specific_login];
If you want to allow connections to SQL Server from only a specific client IP address, you can
stop the default endpoint, or deny access to it, and create a user-defined T-SQL endpoint, with
a client IP address and a TCP port.
Stopping default endpoints has the same effect as disabling them in SQL Server
Configuration Manager.

There's more...

In SQL Server 2012, you can create user-defined server roles. We will detail this functionality
later. This interests us for now, because a server role could be used to grant CONNECT
permissions on an endpoint to a group of logins.
The following code creates a user-defined server role, adds a login as a member, and grants
the CONNECT privilege on the default TCP endpoint to the role:
USE [master];
CREATE SERVER ROLE [TCPRole];
ALTER SERVER ROLE [TCPRole] ADD MEMBER [my_login];
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [TCPRole];

Reference: Microsoft SQL Server 2012 Security

Cookbook

Script to list out all User Stored Procedures in database

SELECT NAME
 ,type
FROM dbo.sysobjects
WHERE (type = 'P')
ORDER BY NAME