Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Output list - Lookup columns

In order to see the columns we get from the lookup operation:

Right click on the lookup icon in the execution plan --> Output list


How to write Special Characters?

Question:
Try to type the sign '©', or maybe '¾'.

problem: no button for that.... :(

Solotion:
Press tab + numbers:
Alt 0169 = '©'
Alt 0190 = '¾'

Note: thias is not only SSMS trick... you can try it on notepad also :)

See all of the Special Characters Alt Keyboard Sequences here:

Find all permissions in all levels on a database

Here is a script that find all permissions in all levels on a database for a specific user (@FromUser) and make scripts of the to another one (@ToUser).

Notes:
1. If you want to get the scripts to the current user, put the same name in both parameters @FromUser and @ToUser.
2. Those queris without the user filter will return all the permissions to all users in the database.

SET NOCOUNT ON

DECLARE       @FromUser sysname, @ToUser sysname

SELECT @FromUser = 'FromUserName', @ToUser = 'ToUserName'

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'

--Role Memberships:
SELECT 'EXEC sp_addrolemember @rolename ='
              + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1)
              + QUOTENAME(@ToUser, '''') AS '--Role Memberships'
FROM   sys.database_role_members AS rm
WHERE  USER_NAME(rm.member_principal_id) = @FromUser
ORDER BY rm.role_principal_id ASC

--Object Level Permissions:
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
              + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' +QUOTENAME(obj.name)
              + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
              + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@ToUser) COLLATE database_default
              + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM          sys.database_permissions perm
JOIN          sys.objects obj                          ON perm.major_id = obj.[object_id]
JOIN          sys.database_principals usr ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN     sys.columns cl                           ON cl.column_id = perm.minor_id AND cl.[object_id] =perm.major_id
WHERE  usr.name = @FromUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC


--Database Level Permissions
--D = Deny, R = Revoke, G = Grant, W = Grant With Grant Option
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
       + SPACE(1) + perm.permission_name + SPACE(1)
       + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@ToUser) COLLATE database_default
       + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM   sys.database_permissions AS perm
       INNER JOIN
       sys.database_principals AS usr
       ON perm.grantee_principal_id = usr.principal_id
WHERE  usr.name = @FromUser
AND    perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC

-- Schema Level Permission
SELECT CASE
                     WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
                     ELSE 'GRANT'
              END
                           + SPACE(1) + perm.permission_name --CONNECT, etc
                           + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
                           + QUOTENAME(SCHEMA_NAME(major_id))
                           + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@ToUser) COLLATE database_default
                           + CASE
                                  WHEN perm.state <> 'W' THEN SPACE(0)
                                  ELSE SPACE(1) + 'WITH GRANT OPTION'
                             END
                     AS '-- Schema Level Permission --'
from sys.database_permissions AS perm
       inner join sys.schemas s
              on perm.major_id = s.schema_id
       inner join sys.database_principals dbprin
              on perm.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema
and dbprin.name = @FromUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC


--Server Level Permissions
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_nameCOLLATE SQL_Latin1_General_CP1_CI_AS
        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM    sys.server_permissions AS server_permissions WITH (NOLOCK)
        INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id =server_principals.principal_id
WHERE   server_principals.type IN ('S', 'U', 'G')
AND           server_principals.name = @FromUser
ORDER BY server_principals.name ,
        server_permissions.state_desc ,
        server_permissions.permission_name;

ISNUMERIC issue: Conversion failed when converting the nvarchar value to data type int

SELECT ... ,CONVERT(int, CAST(MyColumn AS float))   [Points] 
FROM ...
WHERE ISNUMERIC(MyColumn) = 1

Error:
cast failed: Conversion failed when converting the nvarchar value to data type int

But..... we check that ISNUMERIC(MyColumn) = 1, so why it is failed?

Cause:
Some charactes that are not numbers are return from ISNUMERIC as numeric, for example:

select
       ISNUMERIC('-'),
       ISNUMERIC('+'),
       ISNUMERIC('$'),
       ISNUMERIC('.'),
       ISNUMERIC(','),
       ISNUMERIC('\')

will return 1 to all!


Solution: TRY_PARSE
in the conditions, check WHERE TRY_PARSE (MyColumn AS float) IS  NOT NULL

SELECT ... ,CONVERT(int, CAST(MyColumn AS float))   [Points] 
FROM ...
WHERE TRY_PARSE (MyColumn AS float) IS  NOT NULL

TRY_PARSE tries to parse:
  • if it can parse - it return the pared value,
  • if not - it return NULL, and not fail the query!!!
so, if we check thet TRY_PARSE IS  NOT NULL, we can do the casting in the select.