SQL Server: Resync Users to Logins after Restore

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Auto Close and why you want it OFF

So I was perusing my SQL Server Error Logs on my web server and found all my databases restarting. 



All night. 



Every 2 seconds.  



It turns out that the reason for this is that the database is set to automatically shut down whenever there are no more connections.  In a web environment, that means every single time there is a page hit.



We need to turn this off.  Now.



Open SQL Server Management Studio and log in.

From the Object Explorer select

• Server




• Databases




• DatabaseName → Properties

Make sure Auto Close is set to False.





...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

SQL Server: Friendly Time Differences

Ok, so I always like the Facebook smart date displays, like

posted by Bob Loblaw about 12 minutes ago

So I set about recreating this on my own sites in SQL Server.  Here is a fairly easy way to produce this kind of friendly time differences.




Select *
,iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days  ',
''
)+
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) % 24 as varchar(90))+' hours  ',
''
)+
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) % 60 as varchar(90))+' minutes  ',
''
)+
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) % 60 as varchar(90))+' seconds ago.',
''
)
[friendlytime]
from errorlog
--where errdate > DATEADD(HOUR, -1, GETDATE())
order by errdate




This produces a nice, friendly time difference that is better than a list of raw dates for human interpretation.

errdate	page	errmessage	friendlytime
1961-06-17 11:07:21.820	TEST	THIS IS A TEST ERROR MESSAGE	18993 days  0 hours  20 minutes  36 seconds ago.
2010-06-17 11:06:05.470	TEST	THIS IS A TEST ERROR MESSAGE	1096 days  0 hours  21 minutes  52 seconds ago.
2012-06-17 11:04:41.123	TEST	THIS IS A TEST ERRORMESSAGE	365 days  0 hours  23 minutes  16 seconds ago.
2013-06-17 11:07:59.197	TEST	THIS IS A TEST ERROR MESSAGE	20 minutes  58 seconds ago.

Alternately, if you don't care to display all the way down to seconds when the time span is many days, you can nest it like this to only show the most relevant time gap.




iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days ago',
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) as varchar(90))+' hours ago',
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) as varchar(90))+' minutes ago',
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) as varchar(90))+' seconds ago.',
'now'
)
)
)
) [friendlytime2]


This produces a result as follows:

18993 days ago

1096 days ago

365 days ago

38 minutes ago

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Best SQL Server Backup Script Evah

-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(select
    'BACKUP DATABASE ['+name+
    '] TO  DISK = N''J:\Backups\'+name+
    '.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'''+name+
    '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10',
    0
from
    master.sys.databases
where
    owner_sid <> 0x01 and state_desc='ONLINE'
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Temp Tables in SQL Server

We all know you can create variables in SQL Server...


declare @customer varchar(20)

set @customer='a customer'



...but what if there is a need to store more complex data?

As it turns out, there is an easy way to accomplish that as well.




declare @csrlist Table(customer varchar(20), CSR varchar(25), counts int)


--get the counts of customer service reps orders for each customer.

insert into @csrlist (customer, csr, counts)
(
select distinct customer, Csr, COUNT(1) counts
from Purchase_Order
where Csr is not null
group by customer, csr
) 

The resulting in-memory table can be inserted to, deleted from, updated, just like any real data table.


--find the CSR with the most orders for each customer

insert into @csrlist2 (customer, CSR)
    (select customer, Csr from @csrlist A where counts=(select MAX(counts) from @csrlist B where a.customer=b.customer))




---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Cannot execute as the database principal.

Cannot execute as the database principal because the principal "username" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I am going to read your mind now.

1. You recently backed up your database copy-only and moved it to another server or development box. 


2. You're attempting to perform an  "Execute As..." command.


3. Your software has been running for some time and this new error just started cropping up after you "refreshed" your copy of the database (from production?).


4. You looked at the server logins, and the database users, and they seem to match (there is a login with the same name as the user).

What happened is that the SIDs (Security IDs) from the server login does not match the database user of the same name.  Remember that LOGINS are stored at the server level and USERS are in the databases.



What you need to do is re-create the user in the database (and reassign any roles and permissions).

USE [myDB]

GO



/****** Object:  User [myUser]    Script Date: 01/14/2013 18:21:22 ******/

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myUser')

DROP USER [myUser]

GO



USE [myDB]

GO



/****** Object:  User [myUser]    Script Date: 01/14/2013 18:21:22 ******/

GO



CREATE USER [myUser] FOR LOGIN [myUser] WITH DEFAULT_SCHEMA=[dbo]

GO

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

SQL Server Matching on NULL parameter

So I have this query where I am trying to select on customer, unless the user doesn't enter a parameter for customer.  When they leave it blank, we want to see all customers.



So normally I would do it like this:




Select * from Orders where Customer=@customer
And then to handle the null parameter I would change it like this:




Select * from Orders where ((Customer = @customer) OR (@customer is null))
 This works great but then I came across this way of making it simpler.




Select * from Orders where Customer = isnull(@customer, Customer)
The isnull()  effectively handles the case where the parameter (@customer) is null by replacing it with the content of the [Customer] data column, matching to itself!  Problem solved!





...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.