SQL Server on GCP - You do not have permission to run the RECONFIGURE statement

Error message:

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 105 [Batch Start Line 0]

User does not have permission to perform this action.

Msg 5812, Level 14, State 1, Line 2

You do not have permission to run the RECONFIGURE statement.

The case:

Trying to set configurations (sp_configure and RECONFIGURE)

Solution:

There are no permissions to run sp_configure and RECONFIGURE.

Configuring database flags is done in the GCP portal.

Linux: switch user to the root user

 sudo su

SAP (sybase) RS: Permission error while using rs_init

Error message:

Permission denided. You must either be the database/object owner or process any of the following roles in order to perform this operation: sa_role, replication_role

failed to execute 'dbcc is_replication_enabled' as user RS1_RSSD_prim

Solution:

Grant required roles to the relevant users:

use RS1_RSSD

go

grant role sa_role to RS1_RSSD_prim

go

grant role replication_role to RS1_RSSD_prim

go

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;

Script fpr Role creation including permissions

-- set here the role name:

declare @RoleName varchar(50) = 'ROLE_NAME'

declare @RoleScript varchar(max)

SELECT @RoleScript = 'CREATE ROLE ' + @RoleName + char(13)

SELECT @RoleScript = @RoleScript + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS

FROM   sys.database_permissions prm

JOIN      sys.database_principals rol ON prm.grantee_principal_id = rol.principal_id

WHERE  rol.name = @RoleName

PRINT @RoleScript

Remote table-valued function calls are not allowed

Error message:

Remote table-valued function calls are not allowed

The reason:

When selects from remote tables (linked servers) - the table must be called with an alias.

--error:

SELECT ... FROM LinkedServer.DBNAME.dbo.Tablename

-- ok:

SELECT ... FROM LinkedServer.DBNAME.dbo.Tablename t

Explanation:

I don't know, and I also don't know if anyone knows.

Hash data is SQL using HASHBYTES

HASHBYTES('<hash algorithm>', <string text or column>)
http://msdn.microsoft.com/en-us/library/ms174415.aspx

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

Hashing vs Encryption:

http://copypastenet.blogspot.co.il/2014/07/hashing-vs-encryption.html

Hashing vs Encryption

Encryption is a two way process.

Hashing is unidirectional.

In order to find the source value of hashed text, 

we need to hash all the optional strings (for example: hash a column data from a table), and to compare to the hashed string.

REVERT

REVERT - Switches the execution context back to the caller of the last EXECUTE AS statement.

SELECT SUSER_NAME(), USER_NAME(); -- sa, dbo

EXECUTE AS USER = 'myUser';

SELECT SUSER_NAME(), USER_NAME(); -- myUser, myUser

REVERT;

SELECT SUSER_NAME(), USER_NAME(); -- sa, dbo

http://msdn.microsoft.com/en-us/library/ms178632.aspx

Property Owner is not available for Database

Error message:

Property Owner is not available for Database '[...]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

Explanation:

For some reason, the owner of the DB was set to 'UNKNOWN'.

Check it using sp_helpdb:

Solution:

Change the DB owner to a valid login, using sp_changedbowner.

USE DBNAME

GO

sp_changedbowner 'sa'

GO