learnSQLwithME

Hi guys! This post is simply on PLSQL permissions. Specifically, how to script out users. In Postgres, users can have access to: a sequence a table a schema a cluster This example covers giving users access to a sequence, table and schema only. Permissions to a cluster are obtained differently; these permissions are created in AWS (Amazon Web Services). Note : In Postgres, make sure that your Postgres connection has read/write permissions by right clicking the cluster and selecting properties to view the user under the 'connections' tab. Locate the database you want to access under the appropriate cluster > right click the database > choose 'Grant Wizard'. a) Select all the objects. b) select the grantee (the user you want to have access to the database) + the level of permissions they need (which is SELECT in this example). c) copy the text results in step 3 of 3 and paste it into Notepad++. ...

Hi guys! This post is simply on TSQL permissions. Specifically, how to script out users and logins. Users are specific to a SQL server instance while Logins can be used across a domain (via Active Directory). Let's get started on how to script users. This script has only one step. --Check if ##stage tables exists, and if it does, drop it If exists ( Select name from tempdb..sysobjects where name = '##scriptlogins' ) Drop Table ##scriptlogins Go --Create table that lists all staging tables to be purged Create table ##scriptlogins (listid int identity ( 1 , 1 ), script nvarchar ( max )); Insert into ##scriptlogins SELECT 'Use [' +[ name ]+ ']' + char ( 13 ) + char ( 10 ) + ' If exists (Select name from tempdb..sysobjects where name = ''##Users'') Drop Table ##Users If exists (Select name from tempdb..sysobjects where name = ''xcur'') close xcur create table ##users ( r...

Hi guys! This post is simply on TSQL permissions. Specifically, how to script out users and logins. Users are specific to a SQL server instance while Logins can be used across a domain (via Active Directory). Let's get started on how to script logins. Step 1 SELECT 'IF (SUSER_ID(' + QUOTENAME (SP. name , '''' )+ ') IS NULL) BEGIN CREATE LOGIN ' + QUOTENAME (SP. name )+ CASE WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' + CONVERT ( NVARCHAR ( MAX ),SL.password_hash, 1 )+ ' HASHED, CHECK_EXPIRATION = ' + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY = ' + CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END ELSE ' FROM WINDOWS WITH' END + ' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL...

Hi guys! Today's post is a very common request. What do you do if you have to add a SQL user to an instance, but don't know what the password is? It's actually an easy fix. First, you need to find the SID for the SQL user so you can map the user irregardless of the instance you are on. --Step 0 SELECT SUSER_SID(' yourSqlUser ');   GO    Note: You need to copy the result from the results pane in SSMS. I.E.: 0x6FE56AD1A3AA6C40A1336814621ED733 Then you have to run 3 T-SQL scripts that will generate the permissions you need. You will need to put the results from the results pane together. --Step 1 SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+ CASE  WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = '  + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' EL...