The Wiert Corner – irregular stream of stuff

A few short scripts checking out the cause for TFS Integration Tools refuses to start despite an admin being able to create a TEST database.

Get a list of names having sysadmin access to the server from Bru Medishetty:

[sourcecode language=”sql”]
SELECT NAME, isntname FROM SYSLOGINS
WHERE sysadmin = 1 and hasaccess = 1
[/sourcecode]

Check the permissions for a specific database (thanks Arnie Rowland):

Note that if a user has a default database, but no permission for that database, you can get a hard to find login error as List All Permissions a User Has in SQL Server Database and Error 4064 explains.

[sourcecode language=”sql”]
SELECT * FROM fn_my_permissions(NULL, ‘master’)
[/sourcecode]

CONTROL SERVER is a special SERVER wide permission.

Two notes why you should be careful with CONTROL SERVER:

• it implies any permission as this table shows (thanks User Aaron Alton)
• Potential Security Exploit Using CONTROL SERVER Permissions in SQL Server (thanks K. Brian Kelley who also wrote this first script)

[sourcecode language=”sql”]
SELECT login.name, perm.permission_name, perm.state_desc
FROM sys.server_permissions perm
JOIN sys.server_principals login
ON perm.grantee_principal_id = login.principal_id
WHERE permission_name = ‘CONTROL SERVER’;
[/sourcecode]

Listing all server wide permissions (also by K. Brian Kelley):

[sourcecode language=”sql”]
SELECT
[srvprin].[name] [server_principal],
[srvprin].[type_desc] [principal_type],
[srvperm].[permission_name],
[srvperm].[state_desc]
FROM [sys].[server_permissions] srvperm
INNER JOIN [sys].[server_principals] srvprin
ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN (‘S’, ‘U’, ‘G’)
ORDER BY [server_principal], [permission_name];
[/sourcecode]

Finally a long one: SQL Server 2005: View all permissions (2) – Explode the roles by Jamie Thomson (jamiet) on Twitter while he was working at EMC (his new SSIS Junkie blog is at SQLblog.com – The SQL Server blog spot on the web):

[sourcecode language=”sql”]
WITH perms_cte as
(
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
)
–users
SELECT p.principal_name, p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM perms_cte p
WHERE principal_type_desc <> ‘DATABASE_ROLE’
UNION
–role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM perms_cte p
right outer JOIN (
select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name–,*
from sys.database_role_members rm
INNER JOIN sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
order by 1
[/sourcecode]

–jeroen