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

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