SHAOJINGCAO

Vivi at WordPress.com


Leave a comment

Bulk insert access denied (remote file)

Make Service account  has permission to the file

And set SPN

Try to use SQL authorization, grant bulkadmin server role

2014-10-25 02:17:25.370 Server       The SQL Server Network Interface library could not register the Service Principal Name (SPN)
for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back
to NTLM instead of Kerberos. This
2014-10-25 02:17:25.380 Server       SQL Server is now ready for client connections. This is an informational message; no user action is required.

TCP net_transport with SQL auth_scheme works like sql account

TCP net_transport with NTLM auth_scheme doesn’t work for remote (only works from local server) like our own account
Shared memory NTLM like service account

Ref:

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/10/29/sql-bulk-copy-error-operating-system-error-code-5-access-is-denied.aspx

http://blogs.msdn.com/b/jay_akhawri/archive/2009/02/16/resolving-operating-system-error-code-5-with-bulk-insert-a-different-perspective.aspx


Leave a comment

Replication in SQL Server

Reference:

http://www.codeproject.com/Articles/715550/SQL-Server-Replication-Step-by-Step

http://msdn.microsoft.com/en-us/library/ms151734(v=sql.100).aspx

Publisher

Distributor

Subscriber

Three types of Replication:

1.Snapshot Replication (Snapshot Folder)

2.Transaction Replication (Log Reader Agent)

3.Merge Replication (Trigger Change Tracking Tables)

Transactional Replication requires a primary key constraint on all published tables.

However, Merge and Snapshot Replication do not require primary keys.

Merge replication uses a globally unique identifier (GUID) column to identify each row during the merge replication process. If a published table does not have a uniqueidentifier column with the ROWGUIDCOL property and a unique index, replication adds one. Ensure that any SELECT and INSERT statements that reference published tables use column lists. If a table is no longer published and replication added the column, the column is removed; if the column already existed, it is not removed.


Leave a comment

Get object size in one database

1.sp_spaceused

SELECT

t.NAME AS TableName,

i.name as indexName,

sum(p.rows) as RowCounts,

sum(a.total_pages) as TotalPages,

sum(a.used_pages) as UsedPages,

sum(a.data_pages) as DataPages,

(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,

(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,

(sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

WHERE

t.NAME NOT LIKE ‘dt%’ AND

i.OBJECT_ID > 255 AND

i.index_id <= 1

GROUP BY

t.NAME, i.object_id, i.index_id, i.name

–ORDER BY

— object_name(i.object_id)

ORDER BY SUM(a.total_pages) DESC

SELECT TOP 1 OBJECT_NAME(OBJECT_ID) TableName, st.row_count

FROM sys.dm_db_partition_stats st

WHERE index_id < 2

ORDER BY st.row_count DESC

2. by rowcount

SELECT

t.NAME AS TableName,

i.name as indexName,

sum(p.rows) as RowCounts,

sum(a.total_pages) as TotalPages,

sum(a.used_pages) as UsedPages,

sum(a.data_pages) as DataPages,

(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,

(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,

(sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

WHERE

t.NAME NOT LIKE ‘dt%’ AND

i.OBJECT_ID > 255 AND

i.index_id <= 1

GROUP BY

t.NAME, i.object_id, i.index_id, i.name

–ORDER BY

— object_name(i.object_id)

ORDER BY SUM(a.total_pages) DESC


Leave a comment

dm_exec_query_stats

  1. Currently running processes

select SUBSTRING(t.[text],s.stmt_start/2,

(CASE

WHEN s.stmt_end = -1

THEN LEN(CONVERT(nvarchar(max), t.[text])) * 2

ELSE s.stmt_end

END – s.stmt_start)/2) AS [Query Text],DATEDIFF(MINUTE,login_time,GETDATE()) TotalRuntime_minute,DATEDIFF(MINUTE,last_batch,GETDATE()) Lastbatchruntime_minute,s.*

from sys.sysprocesses s

cross apply sys.dm_exec_sql_text(s.sql_handle) t

where spid>50 and status<>’sleeping’

  1. Top 20 CPU consumed (from the server restart)

select top 20 t.text AS sql_statement, execution_count,     plan_generation_num,     last_execution_time,     total_elapsed_time,  total_worker_time,     last_worker_time,     min_worker_time,

max_worker_time,    total_physical_reads,     last_physical_reads,

min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writes

from sys.dm_exec_query_stats s

cross apply sys.dm_exec_sql_text(s.sql_handle) t

order by s.total_worker_time desc

  1. Top 20 long running (from the server restart)

select top 20 t.text AS sql_statement, execution_count,     plan_generation_num,     last_execution_time,     total_elapsed_time,  total_worker_time,     last_worker_time,     min_worker_time,

max_worker_time,    total_physical_reads,     last_physical_reads,

min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writes

from sys.dm_exec_query_stats s

cross apply sys.dm_exec_sql_text(s.sql_handle) t

order by s. total_elapsed_time desc


Leave a comment

Shrink database file

Cannot shrink log file 2 () because of minimum log space required
full backup and then log backup

Cannot shrink log file 2 () because the logical log file located at the end of the file is in use.

use [database]
ALTER DATABASE [database]SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([logfilename], 1000)
ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT


Leave a comment

Cluster

Reference:http://www.sanssql.com/2011/02/t-sql-queries-to-find-sql-server.html

Find name of the Node on which SQL Server Instance is Currently running
SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) AS [CurrentNodeName]
If the server is not cluster, then the above query returns the Host Name of the Server.

Find SQL Server Cluster Nodes
a. Using Function
SELECT * FROM fn_virtualservernodes()

b. Using DMV
SELECT * FROM sys.dm_os_cluster_nodes

Find SQL Server Cluster Shared Drives
a. Using Function
SELECT * FROM fn_servershareddrives()

b. Using DMV
SELECT * FROM sys.dm_io_cluster_shared_drives


Leave a comment

Connection Timed out 1

Problem:

Connection Timed out with error  New queries assigned to process on Node 0 have not been picked up by a worker thread ..

check eventlog and errorlog

Restarted the services, after that the connection is back but some protocol error in the errorlog

Errorlog:

2014-09-16 01:16:25.840 Backup       Database differential changes were backed up. Database: gg, creation date(time): 2014/06/13(11:10:57), pages dumped: 42, first LSN: 19:169:1, last LSN: 19:171:1, full backup LSN: 19:131:37, number of dump devices: 1, device information: (FILE=1, T
2014-09-16 21:17:04.350 Server       Using ‘dbghelp.dll’ version ‘4.0.5’
2014-09-16 21:17:04.440 Server       **Dump thread – spid = 0, EC = 0x0000000000000000
2014-09-16 21:17:04.440 Server       ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.C292WHPRDCL1INST\MSSQL\LOG\SQLDump0001.txt
2014-09-16 21:17:04.440 Server       * *******************************************************************************
2014-09-16 21:17:04.440 Server       *
2014-09-16 21:17:04.440 Server       * BEGIN STACK DUMP:
2014-09-16 21:17:04.440 Server       *   09/16/14 21:17:04 spid 1640
2014-09-16 21:17:04.440 Server       *
2014-09-16 21:17:04.440 Server       * Deadlocked Schedulers
2014-09-16 21:17:04.440 Server       *
2014-09-16 21:17:04.440 Server       * *******************************************************************************
2014-09-16 21:17:04.440 Server       * ——————————————————————————-
2014-09-16 21:17:04.440 Server       * Short Stack Dump
2014-09-16 21:17:04.500 Server       Stack Signature for the dump is 0x00000000000000C3
2014-09-16 21:17:13.180 Server       External dump process return code 0x20000001.
External dump process returned no errors.

2014-09-16 21:17:13.180 Server       New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuration
2014-09-16 21:18:13.190 Server       New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 120 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuratio
2014-09-16 21:19:13.310 Server       New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 180 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuratio
2014-09-16 21:20:13.340 Server       New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 240 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuratio
2014-09-16 21:21:13.350 Server       New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 300 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuratio
2014-09-16 21:22:13.36………….
………….
2014-09-16 22:15:13.900 Server       New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 3540 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configurati
2014-09-16 22:15:52.180 Logon        Error: 18456, Severity: 14, State: 23.
2014-09-16 22:15:52.180 Logon        Login failed for user ‘YDC292APPSQL’. Reason: Access to server validation failed while revalidating the login on the connection. [CLIENT: 10.24.19.77]
2014-09-16 22:15:52.180 spid129      Error: 18056, Severity: 20, State: 23.
2014-09-16 22:15:52.180 spid129      The client was unable to reuse a session with SPID 129, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this e
2014-09-16 22:15:52.200 Logon        Error: 18456, Severity: 14, State: 23.
2014-09-16 22:15:52.200 Logon        Login failed for user ”. Reason: Access to server validation failed while revalidating the login on the connection. [CLIENT: 10.24.19.77]
2014-09-16 22:15:52.200 spid81       Error: 18056, Severity: 20, State: 23.
2014-09-16 22:15:52.200 spid81       The client was unable to reuse a session with SPID 81, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this er
2014-09-16 22:15:52.220 Logon        Error: 18456, Severity: 14, State: 23.
2014-09-16 22:15:52.220 Logon        Login failed for user ”. Reason: Access to server validation failed while revalidating the login on the connection. [CLIENT: 10.24.19.77]
2014-09-16 22:15:52.220 spid131      Error: 18056, Severity: 20, State: 23.
2014-09-16 22:15:52.220 spid131      The client was unable to reuse a session with SPID 131, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this e
2014-09-16 22:15:52.220 Logon        Error: 18456, Severity: 14, State: 23.
2014-09-16 22:15:52.220 Logon        Login failed for user ”. Reason: Access to server validation failed while revalidating the login on the connection. [CLIENT: 10.24.19.77]
2014-09-16 22:15:52.220 spid127      Error: 18056, Severity: 20, State: 23.
2014-09-16 22:15:52.220 spid127      The client was unable to reuse a session with SPID 127, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this e
2014-09-16 22:15:52.240 Logon        Error: 18451, Severity: 14, State: 1.
2014-09-16 22:15:52.240 Logon        Login failed for user ”. Only administrators may connect at this time. [CLIENT: 10.24.19.77]
2014-09-16 22:15:52.260 Logon        Error: 18451, Severity: 14, State: 1.
2014-09-16 22:15:52.260 Logon        Login failed for user ”. Only administrators may connect at this time. [CLIENT: 10.24.19.77]
2014-09-16 22:15:52.260 Logon        Error: 18451, Severity: 14, State: 1.
2014-09-16 22:15:52.260 Logon        Login failed for user ”. Only administrators may connect at this time. [CLIENT: 10.24.19.77]
2014-09-16 22:15:52.300 spid13s      Service Broker manager has shut down.
2014-09-16 22:15:52.870 spid7s       SQL Server is terminating in response to a ‘stop’ request from Service Control Manager. This is an informational message only. No user action is required.
2014-09-16 22:15:52.880 spid7s       SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
2014-09-16 22:15:52.940 Logon        Error: 17188, Severity: 16, State: 1.
2014-09-16 22:15:52.940 Logon        SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [CLIENT: 10.24.19.77]
014-09-16 22:15:52.940 Logon        SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [CLIENT: 10.24.19.75]
………………
2014-09-16 22:15:52.950 Logon        SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [CLIENT: 10.24.19.77]
2014-09-16 22:15:52.950 Logon        Error: 17188, Severity: 16, State: 1.
2014-09-16 22:15:52.950 Logon        SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [CLIENT: 10.24.19.75]
Cannot close event log because there are still event handle users active.
__
Start up the services
__

2014-09-16 22:15:59.560 spid15s      Starting up database ”.
2014-09-16 22:15:59.610 spid14s      Recovery of database ”(5) is 0% complete (approximately 33 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2014-09-16 22:15:59.710 spid10s      Clearing tempdb database.
2014-09-16 22:15:59.750 Server       A self-generated certificate was successfully loaded for encryption.
2014-09-16 22:15:59.750 Server       Server is listening on [ ‘any’ <ipv6> 78666].
2014-09-16 22:15:59.750 Server       Server is listening on [ ‘any’ <ipv4> 78666].
2014-09-16 22:15:59.750 Server       Error: 26049, Severity: 16, State: 1.
2014-09-16 22:15:59.750 Server       Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\C292WHPRDCL1INST ]. Error: 0x50
2014-09-16 22:15:59.780 Server       Error: 17182, Severity: 16, State: 1.
2014-09-16 22:15:59.780 Server       TDSSNIClient initialization failed with error 0x50, status code 0x40. Reason: Unable to initialize the Shared Memory listener. The file exists.
2014-09-16 22:15:59.780 Server       Error: 17182, Severity: 16, State: 1.
2014-09-16 22:15:59.780 Server       TDSSNIClient initialization failed with error 0x50, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The file exists.
2014-09-16 22:15:59.780 Server       Error: 17826, Severity: 18, State: 3.
2014-09-16 22:15:59.780 Server       Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2014-09-16 22:15:59.780 Server       Error: 17120, Severity: 16, State: 1.
2014-09-16 22:15:59.780 Server       SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

(43 row(s) affected)


Leave a comment

Check Last backup and other db information

1. last backup

SELECT database_id,

CONVERT(VARCHAR(25), DB.name) AS dbName,

CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, ‘status’)) AS [Status],

— last backup

ISNULL((SELECT TOP 1

CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN ‘Transaction log’ END + ‘ ‘ +

LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ‘ days ago’, ‘NEVER’)) + ‘ ‘ +

CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_start_date, 108) + ‘ ‘ +

CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_finish_date, 108) +

‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date,

BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘

+ ‘seconds)’

FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),‘-‘) AS [Last backup]

FROM sys.databases DB

–where name in (‘master’, ‘model’, ‘msdb’, ‘DBAMGMT’)

ORDER BY dbName, [Last backup] DESC, NAME

2. All information

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, ‘status’)) AS [Status],
state_desc,
(
SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name ANDtype_desc = ‘rows’) AS DataFiles,
(
SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.nameAND type_desc = ‘rows’) AS [Data MB],
(
SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name ANDtype_desc = ‘log’) AS LogFiles,
(
SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.nameAND type_desc = ‘log’) AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN ’60 (SQL Server 6.0)’
WHEN 65 THEN ’65 (SQL Server 6.5)’
WHEN 70 THEN ’70 (SQL Server 7.0)’
WHEN 80 THEN ’80 (SQL Server 2000)’
WHEN 90 THEN ’90 (SQL Server 2005)’
WHEN 100 THEN ‘100 (SQL Server 2008)’
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), create_date, 108)AS [Creation date],
— last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN‘Transaction log’ END + ‘ – ‘ +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ‘ days ago’,‘NEVER’)) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20),backup_start_date, 108) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20),backup_finish_date, 108) +
‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘
+ ‘seconds)’
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_idDESC),‘-‘) AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN ‘Fulltext enabled’ ELSE  END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN ‘autoclose’ ELSE  END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN ‘read only’ ELSE  END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN ‘autoshrink’ ELSE  END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN ‘auto create statistics’ ELSE  END AS[auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN ‘auto update statistics’ ELSE  END AS[auto update statistics],
CASE WHEN is_in_standby = 1 THEN ‘standby’ ELSE  END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN ‘cleanly shutdown’ ELSE  END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME

SQL SERVER – Get All the Information of Database using sys.databases

 


Leave a comment

View all permissions

1.1 For only one database

SELECT

case prin.name when ‘dbo’ then prin.name + ‘ (‘+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =‘?’) + ‘)’ else prin.name end AS UserName,

prin.type_desc AS LoginType,

isnull(USER_NAME(mem.role_principal_id),) AS AssociatedRole ,create_date,modify_date

FROM sys.database_principals prin

LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id

WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and

 

prin.is_fixed_role <> 1 AND prin.name NOT LIKE ‘##%’

1.2 The following code runs against ALL the databases using SP_MSForeachdb and all roles for one principal is concatenated in one row

Ref:

http://www.pythian.com/blog/httpconsultingblogs-emc-comjamiethomsonarchive20070209sql-server-2005_3a00_-view-all-permissions-_2800_2_2900_-aspx/

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
 
INSERT @DB_USers
EXEC sp_MSforeachdb
 

use [?]
SELECT ”?” AS DB_Name,
case prin.name when ”dbo” then prin.name + ” (”+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =”?”) + ”)” else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),””) AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ”##%”’
 
SELECT
 
dbname,username ,logintype ,create_date ,modify_date ,
 
STUFF(
 
(
 
SELECT ‘,’ + CONVERT(VARCHAR(500),associatedrole)
 
FROM @DB_USers user2
 
WHERE
 
user1.DBName=user2.DBName AND user1.UserName=user2.UserName
 
FOR XML PATH(”)
 
)
 
,1,1,”) AS Permissions_user
 
FROM @DB_USers user1
 
GROUP BY
 
dbname,username ,logintype ,create_date ,modify_date
 
ORDER BY DBName,username

 

2. 1 You can get list of SYSADMIN users by running following code

SELECT p.name AS [Name] ,

p.type_desc,

p.is_disabled ,

p.create_date ,

p.modify_date ,

p.default_database_name

FROM sys.server_principals r

INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id

INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id

AND p.is_disabled = 0

WHERE r.type = ‘R’

AND r.name = N’sysadmin’

2.2 Get sysadmin serverroles except the service account

BEGIN

DECLARE @Date SMALLDATETIME

SET @Date = CAST(CAST(GETDATE() AS DATE) AS SMALLDATETIME)

SELECT CONVERT(VARCHAR(250), SERVERPROPERTY(‘ServerName’)) AS ServerName, SP1.[name] AS [Login], SP2.[name] AS ServerRole, SP1.is_disabled AS IsDisabled, @Date AS ExtractDate

FROM sys.server_principals SP1

INNER JOIN sys.server_role_members SRM ON SP1.principal_id = SRM.member_principal_id

INNER JOIN sys.server_principals SP2 ON SRM.role_principal_id = SP2.principal_id

WHERE SP1.[name] NOT IN (‘NT Service\MSSQLSERVER’,‘NT SERVICE\SQLSERVERAGENT’,‘NT SERVICE\SQLWriter’,

‘NT SERVICE\Winmgmt’,‘NT AUTHORITY\SYSTEM’,‘NT AUTHORITY\NETWORK SERVICE’)

;

END

 

3.

– If a windows login is part of a windows group then you may need to look at the members of this Windows group to identify who can access this database.

– A user may still be revoked access to database indvidual objects even the role allows access normally.

Following blog has a query to list permissions on indvidual objects

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions–_2800_2_2900_.aspx

 


Leave a comment

Linked server Distributed transaction

 OLE DB provider “SQLNCLI10” for linked server “” returned message “The partner transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “” was unable to begin a distributed transaction.
 
 After enabled network dtc access on remote server by

http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp

OLE DB provider “SQLNCLI10” for linked server “” returned message “No transaction is active.”.
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “” was unable to begin a distributed transaction.

–To be resolved