Blending life, nature and technology.
Posts tagged sql
Microsoft Sql Server Administration…
May 24th
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
Dec 31st
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 Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer 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.