Microsoft Sql Server Administration…

Useful commands I’ve found from various sources for Microsoft Sql Server 2005/2008 include:

EXEC sp_configure
GO
–Instance level principals.
SELECT * FROM sys.asymmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.credentials
SELECT * FROM sys.linked_logins
SELECT * FROM sys.remote_logins
SELECT * FROM sys.server_principals
SELECT * FROM sys.server_role_members
SELECT * FROM sys.sql_logins
SELECT * FROM sys.endpoints
GO

–Database level principals.
SELECT * FROM sys.database_principals
SELECT * FROM sys.database_role_members
GO
ALTER LOGIN sa WITH NAME = hiddenSaAccount
GO
–Check your user execution context
SELECT SUSER_SNAME(), USER_NAME()
GO
–View the list of objects in the database
SELECT * FROM sys.objects
GO
–Change user context
EXECUTE AS USER = ‘AnotherUserName’
GO
–Return to your login
REVERT
GO
–explore database files
select * from AdventureWorks.sys.database_files
select * from master.sys.database_files
select * from msdb.sys.database_files
select * from tempdb.sys.database_files
–explore endpoints
select * from sys.endpoints
select * from sys.tcp_endpoints
select * from sys.http_endpoints
select * from sys.database_mirroring_endpoints
select * from sys.service_broker_endpoints

–Security

SELECT * FROM sys.symmetric_keys
GO

SELECT * FROM sys.certificates
GO

Unable to connect to remote server – error

If you get this message say from Visual Studio when trying to deploy SSRS reports to a SQL Server 2008 database instance, heres how this can be resolved. This is usually caused by kerberos security authentication. Navigate to: C:Program FilesMicrosoft SQL ServerMSRS10.MSSQLSERVERReporting ServicesReportServer Edit the file rsreportserver.config
Make the change below:
<!–<Add Key=”SecureConnectionLevel” Value=”2″/>–>
<
Add Key=SecureConnectionLevel Value=0/>

 Restart Reportserver. You should be able to deploy your reports now to SQL Server Reporting Services 2008.