learnSQLwithME

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...